As has been stated in the comments, formulas (worksheet functions) can’t modify cells.
But if you’re content with displaying the values you want, this is easy.
Assuming that I understand what you want.
If your data are in Column
A, starting in Row 1, then enter
=IF(NOT(ISBLANK(A1)), A1, 1)
=IF(NOT(ISBLANK(A2)), A2, IF(NOT(ISBLANK(A1)), 1, B1+1))
B2, and drag this down to cover the data in Column
A (Row 26, for your sample data)
or beyond (if you want). This gets results like this:
- The first row is different because there is no previous row to look at.
Here, if the value in
A1 is not blank, we display it; otherwise, we display
Stated more generally,
If the value in the cell to the left is not blank, display it;
otherwise, we display a generated, sequentially inserted number.
B2 follows this same general pattern:
if the value in
A2 (the cell to the left) is not blank, display it;
otherwise, display a generated number.
The “else” clause (the “otherwise” formula) is
IF(NOT(ISBLANK(A1)), 1, B1+1)
That checks if the value in the cell to the left and one row up is not blank
– i.e., if this row is the first of a block of blank rows.
If this row is the first of a block of blank rows, we display a
Otherwise, we take the value from the cell above this one (
B1) and add
1 to it (i.e., count).