I'm trying to create a table where the list numbers dynamically update
from 1 to the end of the list. Currently, I have the numbering so that
row one, is shown as "1", and then row two has a "=A1+1" function. I
can then copy the row two formula for the remaining cells, and it adds
accordingly. If I insert a new row, the formulas correct the numbering
as it should.

The problem is, if I start deleting rows, then all the rows below the
deleted row get REF! errors. What's the easiest way to avoid this? I
would like a dynamically stable list that will keep it's numbering
correct as I add and remove rows. Is there a way to do this with a
CELL() function perhaps?

Let me know if I need to explain further.


--
Excl
------------------------------------------------------------------------
Excl's Profile: http://www.excelforum.com/member.php...o&userid=37897
View this thread: http://www.excelforum.com/showthread...hreadid=574316

You can use =ROW() instead.

If your data doesn't start on row 1, adjust the formula to:

=ROW() + x

where x = (starting row # - 1)


"Excl" wrote:


I'm trying to create a table where the list numbers dynamically update
from 1 to the end of the list. Currently, I have the numbering so that
row one, is shown as "1", and then row two has a "=A1+1" function. I
can then copy the row two formula for the remaining cells, and it adds
accordingly. If I insert a new row, the formulas correct the numbering
as it should.

The problem is, if I start deleting rows, then all the rows below the
deleted row get REF! errors. What's the easiest way to avoid this? I
would like a dynamically stable list that will keep it's numbering
correct as I add and remove rows. Is there a way to do this with a
CELL() function perhaps?

Let me know if I need to explain further.


--
Excl
------------------------------------------------------------------------
Excl's Profile: http://www.excelforum.com/member.php...o&userid=37897
View this thread: http://www.excelforum.com/showthread...hreadid=574316

Duke Carey Wrote:
You can use =ROW() instead.

If your data doesn't start on row 1, adjust the formula to:

=ROW() + x

where x = (starting row # - 1)

Thanks for the suggestion, but I'm not sure that will work with what
i'm trying to do. I plan to have multiple lists from 1 to x, and could
potentially add new lists in between. I would rather not edit the
"starting row" for every cell whenever I need a new list. I'm looking
for something that will copy/paste and work (with the first row being
either 1 or 0) and something that will work when you delete a cell.

The Cell reference to the one above sort of works, because it remains
and adjusts through a cut and paste. But when deleting a row, the
reference is messed up because of the missing cell. Is there a way to
reference the current cell, and then go back a row? I tried something
like:

=CELL("contents", "A" & ROW(A2) - 1)

But it won't let you string-concatinate a cell reference. Is there any
way to reference a cell by creating the name for it?

Hope that all made sense.


--
Excl
------------------------------------------------------------------------
Excl's Profile: http://www.excelforum.com/member.php...o&userid=37897
View this thread: http://www.excelforum.com/showthread...hreadid=574316

You can use

=OFFSET(current address,-1,0)+1

So, if your list # starts in cell A2 you put

=OFFSET(A3,-1,0)+1

into A3


"Excl" wrote:


Duke Carey Wrote:
You can use =ROW() instead.

If your data doesn't start on row 1, adjust the formula to:

=ROW() + x

where x = (starting row # - 1)

Thanks for the suggestion, but I'm not sure that will work with what
i'm trying to do. I plan to have multiple lists from 1 to x, and could
potentially add new lists in between. I would rather not edit the
"starting row" for every cell whenever I need a new list. I'm looking
for something that will copy/paste and work (with the first row being
either 1 or 0) and something that will work when you delete a cell.

The Cell reference to the one above sort of works, because it remains
and adjusts through a cut and paste. But when deleting a row, the
reference is messed up because of the missing cell. Is there a way to
reference the current cell, and then go back a row? I tried something
like:

=CELL("contents", "A" & ROW(A2) - 1)

But it won't let you string-concatinate a cell reference. Is there any
way to reference a cell by creating the name for it?

Hope that all made sense.


--
Excl
------------------------------------------------------------------------
Excl's Profile: http://www.excelforum.com/member.php...o&userid=37897
View this thread: http://www.excelforum.com/showthread...hreadid=574316

Perfect!! That's exactly what I was looking for! Thanks for the help.


--
Excl
------------------------------------------------------------------------
Excl's Profile: http://www.excelforum.com/member.php...o&userid=37897
View this thread: http://www.excelforum.com/showthread...hreadid=574316