Sequential Cell Numbering

[EW64]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to create an Excel spreadsheet with one column indicating sequential line numbers; 1, 2, 3, etc.. My goal is to have a formula where, for example, the 6th line down displays 6 by adding 1 to the line 5 above it. The reason is if there are 50 lines I don't have to number each one manually but the problem I'm encountering is if one line is deleted then all subsequent lines show "#REF!" error message. The formula I created is "=IF(A5>0, A5+1, A4+1)", with the idea that if line 5 were deleted, the formula would look to the next line up (4) and resume the sequence; by deleting the line the result is "=IF(#REF!>0,#REF!+ 1, A6+1)". The problem as best I can determine is by deleting line 5 it doesn't update all formulas below line 5. Any suggestions, or other formulas I should use. Thanks

 

[John_McGhie_[MVP]]

This is such a pain It's easy in VBA, but you don't have any of that...

You have to test for the error.

The following works:

=IF(ISERR(A5),A4+1,A5+1)

Of course, you can delete only ONE row at a time. But you could extend the
concept with nesting...

Essentially, you have to test "up" the column until you find a good one and
use that.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I'm trying to create an Excel spreadsheet with one column indicating
sequential line numbers; 1, 2, 3, etc.. My goal is to have a formula where,
for example, the 6th line down displays 6 by adding 1 to the line 5 above it.
The reason is if there are 50 lines I don't have to number each one manually
but the problem I'm encountering is if one line is deleted then all subsequent
lines show "#REF!" error message. The formula I created is "=IF(A5>0, A5+1,
A4+1)", with the idea that if line 5 were deleted, the formula would look to
the next line up (4) and resume the sequence; by deleting the line the result
is "=IF(#REF!>0,#REF!+ 1, A6+1)". The problem as best I can determine is by
deleting line 5 it doesn't update all formulas below line 5. Any suggestions,
or other formulas I should use. Thanks

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Bob Greenblatt [MVP]]

This is such a pain It's easy in VBA, but you don't have any of that...

You have to test for the error.

The following works:

=IF(ISERR(A5),A4+1,A5+1)

Of course, you can delete only ONE row at a time. But you could extend the
concept with nesting...

Essentially, you have to test "up" the column until you find a good one and
use that.

Cheers



--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

Pardon me for jumping in her late, but instead of the formula you are
using, try =row(). That will give you the row number wich will stay
where you want it wne you delete lines.

 

[John_McGhie_[MVP]]

Pardon me for jumping in her late,

Unforgiveable!! Not the "jumping in" bit, the "late" bit.

You any idea how long I tried to get that to work? It never occurred to me
that ROW would work without an argument

but instead of the formula you are
using, try =row(). That will give you the row number wich will stay
where you want it wne you delete lines.

So =ROW()-4 if you do not want the cell number to be the actual row number
in the spreadsheet ...

Many thanks

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]