Formula to auto-assign number

  • Thread starter Thread starter tomhelle
  • Start date Start date
T

tomhelle

I have a spreadsheet that lists parts and associated quantities. The user
will be able to add and delete parts from the list. I am looking for a
formula that will automatically assign a “dash number†to the parts. The dash
numbers will be in numerical order and skip any part that has a quantity of
0.

For example:

Original Part Qty Automatically assigned “dash numberâ€

3 -1
1 -2
0
0
2 -3

The user may change the quantities on the list therefore I need to formula
to automatically reassign the “dash number†based on the user’s input. For
example:

Revised Part Qty Automatically assigned “dash numberâ€

2 -1
0
0
7 -2

Many thanks in advance!
 
Put this in B1 (assuming your first number is in A1):

=IF(OR(A1=0,A1=""),"","-"&COUNTIF(A$1:A1,"<>0"))

then copy down as required.

Hope this helps.

Pete
 
Back
Top