Excel - Automatically Increment Row Numbers

Asked By Je on 25-Apr-07 06:52 AM
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 replied on 25-Apr-07 07:21 AM
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

Mike replied on 25-Apr-07 07:24 AM
Number your rows using:-


And drag down - problem solved.

Je replied on 25-Apr-07 07:34 AM
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 replied on 25-Apr-07 08:01 AM

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


Roger Govier
MartinW replied on 25-Apr-07 08:02 AM
You're welcome Jen, glad we could help.

MartinW replied on 25-Apr-07 08:32 AM
Touché!! Too easy!!  <bg>
Bill Smith replied on 18-Nov-08 06:05 PM
what if instead of just having a '1' in the cell you had, say, "S001".  when that runs down the column it looks like:






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

thanks in advance!!