Formula when update when row inserted.

[klixcel]

Hey, I am admittedly really new to excel, so it's probably a really simple mistake. After much web searching cannot find the answer to this. I am trying to create simple groups of sequential numbers that will auto update when I insert a new row. Easy I thought. So I enter 1 as a value in the top row (say A1) of a section and then enter the formula =A1+1 in the next row. I copy paste that for the length of the section I want into the column below and everything auto adjusts fine. This is where the problem begins. If I insert a row, the formulas below don't adjust leaving them referencing the row that was previously above and not currently. If I just enter A1 as a value and copy paste down. The cells all update A2, A3, A4... relative to their row, and when I insert a line they all bump up the chain, but when I add the "+1" it stops working.

I know it's probably a rookie mistake, but whenever I search the solution I get results for constants ($A$1), not what I want.
Thanks in advance for your help and patience with a noob.

Chris.

[Richard Buttrey]

Hi

Does

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

in A2 copied down help?

[klixcel]

Hi

Does

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

in A2 copied down help?

Hey Sorry Guys.
I did manage to get this to work. Does exactly what I was looking for. Thanks for all the help.

[klixcel]

Handy, but not quite what I'm looking for. Noticed I typed the title of my thread wrong, should read "Formula won't update when row inserted". Here's a sample of what I am trying to do.

Thanks.
Chris.

[JeteMc]

If I understand correctly then it seems that after your group(s) of sequential numbers are created your could copy and paste special (values) as in the following copy of your file:
Copy of Numbering Sample.xlsx
Let me know if you have any questions.

[klixcel]

If I understand correctly then it seems that after your group(s) of sequential numbers are created your could copy and paste special (values) as in the following copy of your file:
Attachment 438746
Let me know if you have any questions.

The file just comes up with sequential numbers and no formulas, this causes the same problem of not updating the numbering when a new row is inserted.

Chris

[JeteMc]

Perhaps if you would add your expected outcomes after a row has been inserted, say in column D, to your example someone will know how to do what you are requesting.

[Gatti]

I don't get what you really need with your instructions:

If I insert a row here the number 5 will bump down to A18 instead of changing to 6.

[klixcel]

I have a list of numbers in column A. Say 1-10. I want to be able to insert a row somewhere in the middle of that list and have the numbers change accordingly. I thought it would work to reference the first cell (A1) and add "1" to it. It works initially creating the proper formulas as I copy paste them (=A2+1, =A3+1, =A4+1...), but if I insert a new row in between the formulas don't correct to reference the new rows. It should be really simple, but I can't get it to work.

Thanks.
Chris

[Richard Buttrey]

I don't get what you really need with your instructions:

Me neither!

In the attached, which uses the offset formula I gave in #2, when you insert a row 5 and either enter 5 in A5 or copy A4 down the other rows renumber.

[Gatti]

Or you can just put the formula like:

=ROW()-1

[Richard Buttrey]

Or you can just put the formula like:

=ROW()-1

Even better!

Provided of course the numbering is tied to row numbers and not some other standard.