Sequence Numbering

Edmund Seet · Oct 16, 2003

This is the standard formula I put in column A to return
sequence numbering.

Row Col_A Col_B
1 1 Apple
2 =A1+1 Banana
3 =A2+1 Orange
4 =A3+1 Pear
5 =A4+1 Pineapple
6 =A5+1 Strawberry
7 =A6+1 Watermellon

But if I blankout B4 & B5(clear content "Pear"
& "Pineapple"), I need A4 & A5 to return blank, while A6
to continue sequence numbering by returning value 4.
What's the correct formula?

I tried, A2= "=IF(B2="","",A1+1)" but it returns #VALUE!
whenver the cell B of the same row is blank.

I want it to appear like this:
Row Col_A Col_B
1 1 Apple
2 2 Banana
3 3 Orange
4 <blank> <blank>
5 <blank> <blank>
6 4 Strawberry
7 5 Watermellon

What would the formula be? And do I need to turn on
Tool>Option>Calculation....Iteration check box?

TIA
Edmund Seet

Biff · Oct 16, 2003

Hi Edmund,

Try something like this starting in A2:

=IF(B2="","",MAX(A$1:A1)+1)

Biff

Edmund Seet · Oct 17, 2003

Thanks Biff. It worked ... perfect.

Edmund Seet