looking for a more powerful way to number rows

[Plasmech]

In almost all of my spreadsheets, my first column is an line number / index number column. I code this as such:

A1 1
A2 =A1+1
A3 =A2+1
A4 =A3+1
A5 =A4+1
A6 =A5+1

But this is not a very powerful or forward-looking way to do it, however.

If I delete the 4th row, for example, rows 5 and 6 will return #REF!.

...and if I move a row, the numbers will no longer be sequential.

There must be a better way to do this, something embarrassingly obvious that I'm missing.

Any suggestions?

Thanks.

 

[Jeffrey Mahoney]

A2 = =OFFSET(A2,-1,0)+1

 

[Akuini]

I don't quite understand the criteria, but this will give you the row index of a cell:

=ROW()

 

[Plasmech]

That, sir, is it. Thanks!

 

[JoeMo]

I'm late to the party here, but since you are using MS 365 you could generate sequential row numbers by entering a simple formula and allowing it to spill down. The numbers will remain unchanged by row deletions or, say, cutting a cell or cells. In the example below, the first 100 cells in col A are numbered sequentially from 1-100 when the formula in A1 is entered.

Book1.xlsm
	A
1	1
Sheet2

Cell Formulas
Range	Formula
A1:A100	A1	=SEQUENCE(100,1,1,1)
Dynamic array formulas.

 

[Plasmech]

A2 = =OFFSET(A2,-1,0)+1

Getting back to this (sorry for the delay), it does not work on the first row. It works on the 2nd (and subsequent) row, as long as there's a 1 in the first row. Maybe I am doing something wrong?

Thanks.

 

[Jeffrey Mahoney]

Well, the offset function is trying to look at the cell above. If you have this in cell A1, it's trying to reference a non-existent cell. You could try this
Cell A1: =IFERROR(OFFSET(A1,-1,0),0)+1

 

[Plasmech]

Well, the offset function is trying to look at the cell above. If you have this in cell A1, it's trying to reference a non-existent cell. You could try this
Cell A1: =IFERROR(OFFSET(A1,-1,0),0)+1

I am getting this:

 

[Jeffrey Mahoney]

The formula you are showing for Cell A4 should be: =IFERROR(OFFSET(A4,-1,0),0)+1

I can't see the formula you have in Cell A5

 

[Plasmech]

That gives me a #VALUE! in A4. The rest of the column is fill-down.