=IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="",1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)

**The ADDRESS function**

You should already be familiar with the ROW and COLUMN functions. You can either put a reference (cell address) inside them such as =ROW(A2) which will return 2 or =COLUMN(D3) which will return 4. Used by themselves, =ROW() or =COLUMN() will automatically return the row or column number of the cell that the formula is placed in.

The ADDRESS function takes this a step further. =ADDRESS(ROW(),COLUMN()) will return the address of the cell it is placed in.

**The INDIRECT function**

As you can see from the formula I wrote at the top, the INDIRECT function is combined with the ADDRESS function. I'm using =ADDRESS(ROW()-1,COLUMN()) to refer to the cell above the row my formula is placed in, the INDIRECT function is used to refer to the value in that cell.

Here is a picture with INDIRECT in action.

I have added a column to the above ADDRESS formula to refer to cell E3 as in =ADDRESS(ROW(),COLUMN()+1). Then in cell B2, the INDIRECT function is used to refer to cell D3 which in turn refers to cell E3. Yes, INDIRECT is somewhat ...indirect!

Now you should be getting a feel for how my original formula works. The IF function determines that if the above row is blank (""), the result should be 1. If not, add 1 to the cell value above.

Enter the formula into a cell below a bank row and drag down. The numbers will appear in order.

Here's a modified formula to work in the top row also.

=IF(ISERROR(ADDRESS(ROW()-1,COLUMN())),ROW(),IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="",1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1))

This formula works on the principle that there is no row above the top row. As this would result in an error, the formula returns the regular row number. In the case of the top row, this just happens to be Row 1.