tl;dr: Formula to number rows that doesn't break when some "rows" are made of multiple, merged spreadsheet rows.
Scenario: I am making a check-list (of things to look at when apartment-hunting) in Numbers 3 that involves some "nesting" by merging cells. It's more efficient to show than explain, so here's a little dummy:
|| A | B | C | D | E
-----------------------------------------------
-----------------------------------------------
1 || # | Item | Apt1 | Apt2
-----------------------------------------------
2 || 1 | Sink works | [√] | [√]
-----------------------------------------------
3 || | | Heat | [√] | [√]
---- - -----------------------------
4 || 2 | Has | A/C | [√] | [√]
---- - -----------------------------
5 || | | Gas | [√] | [√]
-----------------------------------------------
6 || 3 | Door locks | [√] | [√]
Problem: Normal ways of putting in a row number break because of the way Numbers handles merged cells. For example, A6=SUM(A5,1)
would return 1, because Numbers thinks A5
and A4
are empty (whereas A3
contains 2).
Best Answer
Using way more math than should be needed, there is a formula that works. In the dummy spreadsheet above, you would put this in
A3
and then copy it into all subsequent rows in bulk, just as you'd expect: