Automatically Increment Row Numbers

[Guest]

I have a spreadsheet with a list of names on a waiting list, and I have each
row numbered incrementally using the formula =A1+1 etc. However, when I
delete a row (when someone 'drops out' of the waiting list) the row numbers
below come up with an error (#REF!).

I have also tried to number the rows using Excel's automatic list, but when
I remove a row the row number also disappears (ie row number 9 follows
straight after 7, rather than changing to 8).

What I would like the spreadsheet to do is this:

I have a list:

1 John Smith
2 Joe Blogss
3 Katie Merryfield
4 Ian Jones
5 Carl Huges
6 Doris Brown
and so on...

When Ian Jones says he wants to be removed from the waiting list, I need to
delete his row so that the sheet now says:

1 John Smith
2 Joe Blogss
3 Katie Merryfield
4 Carl Huges
5 Doris Brown

Please could anyone tell me how to do this? Any help is much appreciated.
I have been scanning this and other sites, and my Excel Formula 'bible', but
can't find out how to do this seemingly simple thing!

Many thanks

 

[MartinW]

Hi Jen,

Instead of using =A1+1, use =ROW(A1) and drag down.
If you need to start your series in say, row 5, then it would be
=ROW(A5)-4.

HTH
Martin

 

[Guest]

Number your rows using:-

=ROW(A1)

And drag down - problem solved.

Mike

 

[Guest]

Thank you both so much for your help! I can't believe it is actually so
simple, and yet I couldn't find the answer anywhere (spent hours fiddling
about with VB codes and stuff to make it work, and thinking 'it's got to be
easier than this'!)

Thanks again!

 

[Roger Govier]

Hi

Or you could just enter
=ROW(A1)
in cell A5 or anywhere on the sheet.
The number 1 will start from the cell containing ROW(A1)

 

[MartinW]

You're welcome Jen, glad we could help.

Martin

 

[MartinW]

Touché!! Too easy!! <bg>

 

[Bill Smith]

what if instead of just having a '1' in the cell you had, say, "S001". when that runs down the column it looks like:

S001
S002
S003
S004
etc.

when i delete S002 i would like S003 to become S002. does that make sense? how would i do that?!

thanks in advance!!

 

[Rick Rothstein]

Assuming Column A starting in Row 1 - put S001 in A1, then put this formula
in A2 and copy it down...

="S"&TEXT(RIGHT(LOOKUP(2,1/(A$1:A1<>""),A$1:A1),3)+1,"000")