How: automatically re-adjust number series

[Josh W]

I have the following number series:
B1=1000
B2=1005
B3=1010
B4=1015
B5=1020
B6+1025
etc etc all in increments of 5. However, I have some completely random cells
in the same column that I must leave blank. Since I used the fill handle to
copy down the number series, how can I delete random cells and have the
number series readjusted automatically e.g as follows:
B1=1000
B2=blank
B3=1005
B4=1010
B5=blank
B6=1015
Greatly appreciate your help. Excel 2003.

 

[Don Guillett]

in b2=row()*5+1000 and copy down
Then DELETE the blank row(s)

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Josh W]

Sorry Don, that doesn't work at all. 1) I do NOT want to delete the row which
contain other data it's only the cell that is blank. 2) Even when I delete a
row e.g B2, B3 does not autimatically change from 1010 to 1015.

 

[T. Valko]

See if this works fo you:

B1 = manually entered 1000

Enter this formula in B2 and copy down as needed:

=COUNT(B$1:B1)*5+1000

 

[Don Guillett]

or to not show number at blank
=IF(TRIM(B2)="","",COUNT(B$1:B1)*5+1000)

 

[T. Valko]

That causes a circular reference?

 

[Don Guillett]

I put 1000 in b1 and the formula in b2 and copied down.