Looking for help creating dynamic list numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Excl - 22 Aug 2006 19:05 GMT

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.

Signature

Excl

Reply to this Message

Duke Carey - 22 Aug 2006 19:40 GMT

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)

> 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
[quoted text clipped - 10 lines]
>
> Let me know if I need to explain further.

Reply to this Message

Excl - 22 Aug 2006 20:26 GMT

Duke Carey Wrote:

> You can use =ROW() instead.
>
[quoted text clipped - 3 lines]
>
> 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.

Signature

Excl

Reply to this Message

Duke Carey - 22 Aug 2006 21:06 GMT

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

> Duke Carey Wrote:
> > You can use =ROW() instead.
[quoted text clipped - 24 lines]
>
> Hope that all made sense.

Reply to this Message

Excl - 22 Aug 2006 21:16 GMT

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

Signature

Excl

Reply to this Message