3 ms·
CatDancer, the problem is that the system can't always know what you intended: should the range be "greedy" and jump to incorporate the new numbers as you appen
by gruseom 18y ago
CatDancer, the problem is that the system can't always know what you intended: should the range be "greedy" and jump to incorporate the new numbers as you append them, or should it be "strict" and keep to the boundaries you originally gave it? Sometimes the latter behavior is what is desired, and expanding to C2:C11 would be wrong in that case. So what's really needed here is a lightweight way to communicate your intention to the system. (Actually, you can do this in Excel - http://tinyurl.com/26b78 http://tinyurl.com/26b78 - but it's far from lightweight.)
I'm assuming that (in terms of your example) the invoice amounts that you're adding up would form a contiguous range of numbers, and that this range would be bounded by whitespace. That is, you might have some other range that used column C -- say "expenses" -- but it would be lower down, say starting at C15, and there would be at least one blank cell between the two. Is that correct? If it weren't for that lower range, you could just take the sum of the whole column and you'd be good. But it's too inconvenient (and not the "spreadsheet way") to force everything into separate columns.
If the above is correct, how would you feel about being able to define a range with a notation like this: "C2:C✱", meaning "the range of cells that starts at C2 and goes down until it hits whitespace"? Then as you add numbers to C11, C12, etc., the range would automatically expand to include them. But you'd still have to be careful to ensure there was a "moat" of whitespace around your invoice range. If you filled in the last non-whitespace cell before your other table, you'd now have connected the two tables in such a way that "C2:C✱" would leap down to the end of the second table. In other words you'd be lumping "invoices" together with "expenses" which is probably incorrect.