excel inventory formula

[paul51]

Hello to all i am stuck i need help i have a table with headings stock allocate. Actual stock. Picking. Stock number is 10. Allocate. Is 5. Actual stock is 5. Picking is 5. I know the formula to do this deduction but i want the actual stock figure to remain 5 even though i zero the picking figure to start new picking order. I will insert a new column after to add stock when i have sorted this problem with your help i await any help regards

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[paul51]

This is a small sample has you can see the columns are simple just change the picking figure and the stock column changes thats ok i want it to deplete but not to increase when i zero the picking figure has i want to insert new picking figures into it cus the picking figures relates to new orders i have, i will insert a new column for adding stock.after this problemis hopefully solved . What i am trying to create is an basic inventory system i hope someone will solve my problem

I await your kind reply

[paul51]

This is a small sample has you can see the columns are simple just change the picking figure and the stock column changes thats ok i want it to deplete but not to increase when i zero the picking figure has i want to insert new picking figures into it cus the picking figures relates to new orders i have, i will insert a new column for adding stock.after this problemis hopefully solved . What i am trying to create is an basic inventory system i hope someone will solve my problem

I await your kind reply

Sample work book on how i want it to look

[AliGW]

You are going to have to explain yourself a little more clearly. Try doing this in short sentences, because what you have written so far may make sense to you, but it really doesn't to me. Try using bullet points for each of your requirements. Annotating the worksheet to show what you want and expect to happen would help, too.

[paul51]

hi sorry i will try to explain a bit better, i have a table with 5 columns, column 1 physical stock, column 2 allocate, column 3 actual stock,column 4 picking,column 5 addition. what i am trying to do is to put number 5 in the picking column, which then would show up in the allocate column as 5, this would reflect in the pysical stock column, which then would leave me a figure in the actual stock column, then i would add stock in the addition column which would then increase the actual stock in the actual stock column. then i would change the picking figure to start a new order but i dont want it alter the actual stock figure i want that to remain live, so i have a clear view of what i have in stock. i have included a table
many thanks

[peterrc]

Hi paul51,

Please see attached - is this what you want?

Regards

peterrc

[paul51]

You have made me an happy man i was on the verge of insanity you have saved my life i thank you very much i cant give you enough credit only to say thanks

[peterrc]

Hi paul51,

Thanks for the update - glad it's what you wanted.

Don't forget to mark the thread as solved.

Regards

peterrc

[paul51]

I thank you very much i have noticed that when i add a number in the addition column it does not change the actual stock figure the picking figure works great but the addition dont seem to please ad ise thank you

[peterrc]

Hi paul51,

Please see attached for an explanation.

Regards

peterrc

[paul51]

hello i understand now thank you , the table i am making is different i will attach for you to see

[paul51]

Hello there have you found a solution to my problem ???? I tried to wirk your solution into my database but it got so confusing and rather large so i quit it, your table and formulas worked great but it dont suit my project all i want is the correct formula so my inventory works , i hope you can help thank you for all your help so far
Regards

[peterrc]

Hi paul51,

You want the actual stock to reduce when you pick (allocate) a quantity - the formula supplied does this.
However, if you zero the pick (allocate) quantity you do not want the actual stock figure to change.
I do not know a formula that will allow you to do this, so I am not sure if I can assist you any further.
I have added the formulas to the table you supplied in post #12 - copy attached.
If you extend the table and manually change the values and highlight in red what you want to see I'll take another look.

Regards

peterrc

[paul51]

Thank you for all your help i will endevour to check out all what you ha e supplied to me in answers and i will get.back.to you

Thank you very much for.your help