Formula to auto-assign a 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 recalculate the “dash number†based on the user inputs. For
example:

Revised Part Qty Automatically assigned “dash numberâ€

2 -1
0
0
7 -2

Many thanks in advance!

 

[Steve]

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 recalculate the “dash number†based on the user inputs. For
example:

Revised Part Qty Automatically assigned “dash numberâ€

2 -1
0
0
7 -2

Many thanks in advance!

Tom:

I'm not sure whether I just don't understand what you are trying to do or you're holding out on part of the explanation.

As you can see from my sig file, I am a 35 year veteran of the parts industry and for the last 8 years, was a parts manager. So, I understand that you want to keep track of the inventory on hand and re-order to replenish inventory in a timely manner.

How many unique part numbers are involved? Why isn't there any inventory control program instead of having to keep it on an Excel spreadsheet?

What I don't understand is if you're trying to make the QOH (quantity on hand) a part of the part number itself.

Please elaborate on what exactly you are trying to do. I think I have a solution for you, but it's more complex than what you have presented so far.

If you like, you may email me directly. Of course, that will take you away from the Excel group here so respond as you wish.
--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
Unemployed Car Guy - Trying to Earn A Living
35 Years of GM Parts Experience
http://synthetic-oil-tech.com/1690163
(e-mail address removed)

 

[That One Guy]

tomhelle wrote:

Where are you getting your dash numbers from? Is this from a table, or
is there some kind of mathematical logic that I fail to see.

If from a table, try =if(a1=0,"",vlookup(a1,sheet2!$A$1,$D$30,4,0))

Regards,

That One Guy