Formula for Item Number (consective, ignoring blank lines)

[JerBearG]

Hello world!

1st post. I'm trying to generate item numbers for quoted items in my quoting template. It should be simple, but I can't figure out how to skip blank lines (which happen often in our world) and continue counting from the previous number.

Example:
Item # - Data - More Data
1 1" x 2" x 5"
2 1" x 3" x 5"

3 2" x 1" x 10"
4 2" x 2" x 5"


5 3" x 2" x 5"
6 3" x 3" x 5"
7 3" x 4" x 5"

I need code to generate those items numbers (**Sometimes there may be 2 blank lines in a row**). I could do this with VBA, but I want them to be fluid with simple excel code (unless I'm missing an easy way for them to auto update with VBA that isn't slow).

Thanks!

 

[Rick Rothstein]

This will work as long as your first data cell is not in Row 1. Put this formula in cell A2 and copy down...

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

 

[JerBearG]

That looks great! What a good idea. Unfortunately, I can't get it to work. It keeps coming out as 0s. Even if I say, "= one of the 0 cells + 1", it still returns 0. I'm thinking its some formatting error, but I can't figure it out. The number formatting is fine.

 

[Marcelo Branco]

Try this

A2
=IF(B2="","",COUNTA(B$2:B2))
copy down

M.

 

[JerBearG]

Perfect!