incrementing with blank cells in between

[alexmace]

I have data in column A at random intervals. I'd like to auto-increment
a number in Column B only if data appears in column A in the same row.

So:

__ A | B
1 aa | 01
2
3 bb | 02
4 cc | 03
5
6
7
8 dd | 04
9
10


etc

Then, if data were added to any row in column A, the numbers in B would
all update.

Anyone have a simple solution?

 

[Biff]

Hi!

Enter this formula in B1 and copy down as needed:

=IF(A1="","",COUNTA(A$1:A1))

If you want the leading zero you'll have to format specifically for that.

Select the range of cells where this formula will be entered.
Goto Format>Cells>Number tab>Custom
In the little box under TYPE, enter: 0#
OK

Biff

 

[Bob Phillips]

Put 1 in B1.

In B2, add

=IF(A2="","",INDEX($B$1:B1,MAX(IF($B$1:B1<>"",ROW($B$1:B1))))+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter, and copy down.

Format column B as 00.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)