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

Number your rows using:-

=ROW(A1)

And drag down - problem solved.

Mike



"Jen" wrote:

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

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)

--
Regards

Roger Govier


"MartinW" wrote in message
...
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

You're welcome Jen, glad we could help.

Martin

Touché!! Too easy!! <bg

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")

--
Rick (MVP - Excel)


"Bill Smith" wrote in message ...
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!!

Hi,

Another way, if you mean delete the row containing S002 instead of just the
contents of the cell, is to enter
=ROW() in A1, apply a custom number format of "S"000 and copy down.
Adjust the formula to read =ROW()-9 for instance, if you want the series to
start on row 10.

Dave

url:http://www.ureader.com/msg/10356846.aspx