Better autonumbering formula?

[Guest]

I have labels in column A - 1.1, 1.2, 1.3 ... 2.1, 2.2....5.1, etc.

Instead of the A2=A1+.11, is there an alternate formula that will allow for
entering a new row in the middle of a sequence and having the subsequent
entries renumber correctly? When I insert a new line in the middle (e.g.,
between A4 and A5) I get a blank A5 and now A6 shows A4+.1 and not the
desired A5+.1. I'm happy to manually enter A4+.1 into the new A5.

I know I could probably just redrag the autofill, but it becomes tedious and
I've got to think someone smarter than me can figure this out =)

 

[Fredrik Wahlgren]

I have labels in column A - 1.1, 1.2, 1.3 ... 2.1, 2.2....5.1, etc.

Instead of the A2=A1+.11, is there an alternate formula that will allow for
entering a new row in the middle of a sequence and having the subsequent
entries renumber correctly? When I insert a new line in the middle (e.g.,
between A4 and A5) I get a blank A5 and now A6 shows A4+.1 and not the
desired A5+.1. I'm happy to manually enter A4+.1 into the new A5.

I know I could probably just redrag the autofill, but it becomes tedious and
I've got to think someone smarter than me can figure this out =)

Well, here's one approach. Start the Visual Basic Editor, insert a module
and paste the following code

Public Function GetVal() As Double
Dim row As Long

Application.Volatile True
row = Application.Caller.row
GetVal = 1 + row * 0.1
End Function

The function has to be volatile. If not, it won't update correctly when you
insert a new row.

Best Regards,
Fredrik

 

[Fredrik Wahlgren]

=1+ROW()/10

I agree, this is a better solution. I wonder why I din't think of this at
once.

/Fredrik

 

[Harald Staff]

=1+ROW()/10

The usual thing is to NOT renumber those things though. They are most often
unique ID numbers for whatever is on the row.

HTH. Best wishes Harald

 

[Guest]

Actually, because I have the numbering restart elsewhere, I can't use the
absolute position provided by ROW(). However, this gives me an idea to
leverage the relative positions of the items:

=A1+(ROW()-ROW(A1))*0.1

that way, when I insert a row in an existing block, everything underneath
basically increments by .1 automatically.

You're right that new items generally should get a new number, but this is
for a task list/pseudo project plan and in the beginning, when you're adding
new items positionally, the numbers don't have to be locked in until you're
done...

Thanks for the suggestion!

 

[Fredrik Wahlgren]

Row(A1) is always 1 which means your function can be simplified as:
=A1+(ROW()-1)*0.1
which looks nicer.

/Fredrik

 

[Harald Staff]

Hi Fredrik

A1 isn't necessarily A1 forever. The reason for those constructions is that
they handle when rows are inserted/deleted.
=A1+(ROW()-ROW(A1))*0.1
A new row 1 makes it
=A2+(ROW()-ROW(A2))*0.1

Good looking isn't everything ;-)

HTH. Best wishes Harald

 

[Fredrik Wahlgren]

Hi Fredrik

A1 isn't necessarily A1 forever. The reason for those constructions is that
they handle when rows are inserted/deleted.
=A1+(ROW()-ROW(A1))*0.1
A new row 1 makes it
=A2+(ROW()-ROW(A2))*0.1

Good looking isn't everything ;-)

HTH. Best wishes Harald

I just realized I wasn't thinking when I made that reply.

/fredrik