I was writing up the CellAbove trick for the book. You know, the trick outlined at Ken Puls’ place.
First I was going to show how SUM doesn’t handle row insertions immediately above the SUM function itself. So I duly made up a crappy example (if you’ll pardon the crappy pun):
…and then did the screenshot of the row insert, that showed how the formula didn’t adjust:
…and then went to add some ridiculously large number of underwear, for readers who need some point laboring:
…and then pushed enter, and to my amazement saw that my example of “broken” ain’t, no more:
How utterly inconsiderate of Microsoft to fix this. Ruined a perfectly good example that I figure I could have dragged out for another half a chapter. Bugger! Now I have to write something else. God I hope they don’t fix the CONCATENATE function before I finish the book, or I’m screwed.
I just heard from Bill Jelen, who mentions when this gets autocorrected in this really great podcast of his:
He says “Going back to Excel 2003, provided you have a series of three or more numbers, Excel’s going to rewrite the formula to handle the new rows”.
But do check out that link: It’s hilarious. Also check out the follow-up podcasts.
There’s nothing like an Excel nerd with too much time on his hands…