Automatically Increment Row Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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!
 
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)
 
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!!
 
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")
 
Back
Top