Formula to auto-assign number

[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!

 

[Pete_UK]

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

 

[Max]

Assuming source data as posted in A2 down
In B2: =IF(OR(A2={"",0}),"","-"&COUNTIF(A$2:A2,"<>0"))
Copy down

If above helped, pl click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik