Better autonumbering formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 =)
 
Dan in NY said:
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 =)
Click to expand...

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
 
=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
 
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!
 
Row(A1) is always 1 which means your function can be simplified as:
=A1+(ROW()-1)*0.1
which looks nicer.

/Fredrik
 
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
 
Harald Staff said:
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
Click to expand...

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

/fredrik
 
Back
Top