Better autonumbering formula?
 

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" wrote in message
...
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

"Harald Staff" wrote in message
...
=1+ROW()/10


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

/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

"Dan in NY" skrev i melding
...
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 =)

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!

"Harald Staff" wrote:

=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

"Dan in NY" skrev i melding
...
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 =)

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

/Fredrik

"Dan in NY" wrote in message
...
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!

"Harald Staff" wrote:

=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

"Dan in NY" skrev i melding
...
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 =)

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

/Fredrik

"Dan in NY" wrote in message
...
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!

"Harald Staff" wrote:

=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

"Dan in NY" skrev i melding
...
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 =)

"Harald Staff" wrote in message
...
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