I am looking for a way to dynamically number non-blank rows in a quote template (ie Item #).
The Template looks as follows:
B1 C1 D1
Item # Qty Description ...
1 2 Product A
2 1 Product B
3 4 Product C
If I add a row in between item 1 & 2, I would like all items to re-number properly. If I move the row for Product B down to the bottom (basically park it outside the template for possible addition later), I would also like all items to renumber properly.
non-blank rows should not be numbered.
I tried this:
=IF(C32 >0, (ROW(B32)-ROW($B$22)- COUNTBLANK($B$22:B31)),"")
where C32 = is the Qty, and $B$22 is the top of the column in the template where I need to start counting.
This formula works except if I move a row to somewhere at the bottom (creates a circular reference).
Thoughts, ideas or an easier suggestion for creating a dynamic range that goes from the top of the template to the current row.
The Template looks as follows:
B1 C1 D1
Item # Qty Description ...
1 2 Product A
2 1 Product B
3 4 Product C
If I add a row in between item 1 & 2, I would like all items to re-number properly. If I move the row for Product B down to the bottom (basically park it outside the template for possible addition later), I would also like all items to renumber properly.
non-blank rows should not be numbered.
I tried this:
=IF(C32 >0, (ROW(B32)-ROW($B$22)- COUNTBLANK($B$22:B31)),"")
where C32 = is the Qty, and $B$22 is the top of the column in the template where I need to start counting.
This formula works except if I move a row to somewhere at the bottom (creates a circular reference).
Thoughts, ideas or an easier suggestion for creating a dynamic range that goes from the top of the template to the current row.
