Looking for help creating dynamic list numbers

[Excl]

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.

[Duke Carey]

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

[Excl]

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.

[Duke Carey]

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

[Excl]

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