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]

You could try this in Cell A4: =Sum(OFFSET(A4,-1,0))+1

 

[Plasmech]

You could try this in Cell A4: =Sum(OFFSET(A4,-1,0))+1

That appears to work!

The only squawk, for what it's worth, is that you can't use it in row 1. But row 1 for me is always for headers.

 

[Jeffrey Mahoney]

Adding values to a cell with a label produces an error unless you use the SUM function. If you want to remove the error in row 1 then you would have to use the IFERROR instead

 

[Plasmech]

=IFERROR(OFFSET(A1,-1,0)+1,1)

Seems to handle all situations.

Thanks for your help on this!

 

[Akuini]

@Plasmech
Did you try the code I suggested in post #3?

 

[Plasmech]

@Plasmech
Did you try the code I suggested in post #3?

Hi Akuini,

ROW() cannot handle the repositioning of rows, and has to be adjusted depending on where the first row is.

 

[johnnyL]

Hi Akuini,

ROW() cannot handle the repositioning of rows, and has to be adjusted depending on where the first row is.

You sure about that?