Fomula to calculate supply vs Demand - Stock

I have a spreadsheet which uses MS Query to import data from our MRP System. This Spreadsheet has all new orders and qtys required per sales order etc. Each Part number may have several sales orders and different qtys for each. I am looking for help to write a fomula which looks the required qty`s for each sales order & part and check another sheet which has all the avaliable stock to sell on it and populate a column next to the qty req`d with what if anything is available.
Both the orders sheet and the stock sheet are MS Queries which refresh at a given interval so cells may change position.

Cutdown Example of Orders Sheet.

Sales Order No. ¦ Part ¦ Qty Req`d ¦
--------------------------------------
1234 ¦ Apple ¦ 12 ¦
6777 ¦ Apple ¦ 16 ¦
7989 ¦ Pear ¦ 4 ¦

cutdown Example of Stock Sheet.

Part ¦Qty in Stock ¦
----------------------
Apple ¦ 12 ¦
Pear ¦ 3 ¦


Now the fomula should bring back the following

Sales Order No. ¦ Part ¦ Qty Req`d ¦ Qty Avaliable ¦
----------------------------------------------------
1234 ¦ Apple ¦ 12 ¦ 12 ¦
6777 ¦ Apple ¦ 16 ¦ 0 ¦
7989 ¦ Pear ¦ 4 ¦ 3 ¦

Any Ideas?????

Regards
Alan

Try the following in D2 and copy down:

Please Login or Register  to view this content.

Tables I used for testing (also in the attachement.

Please Login or Register  to view this content.

Thanks for your response this works well, but is there a way I can stop it from reporting minus fiqures when there is no stock?

Have you also noticed that if you set the stock level of one part to zero then the next time that part appears it asks for 1x less than needed.


Thanks
Alan

is there a way I can stop it from reporting minus fiqures when there is no stock?

You can format your cells to not show negative numbers - the values represented will remain, just their display will change.

Go to Format / Cells... (or press Ctrl+1 or right click / choose format cells)
Scroll down to Custom in the left pane
Enter #0;"-";0 in the "Type:" box

Custom formatting - very brief tutorial. Each position, separated by semi-colon shows how the value is to be displayed
positive value;negative value;zero value

stock level of one part to zero then the next time that part appears it asks for 1x less than needed.

I'm not quite sure what you mean. The third column shows the original stock minus the amount needed to fill previous orders for that item.

Great thats perfect. Many Thanks for your help on this one. Now I just have to intergrate the real thing.

I did what you said on the attached file. I guess the custom format you used
#0;"-";0 could be any character between the quote or even nothing.

Thanks
Alan

Your guess is correct. You could even have it show "Stock Out" or something similar

BTW the file you attached still has the old formatting.

The only problem I can for see is if the stock list does not contain the Part Name/number then you will get #N/A.

Is there anyway without using conditional formatting to turn this error into something usefull like a zero.

The reason I say without conditional formatting is I have used up the 3 Conditions aready on these cells.

Regards
Alan

You can add a check to see it the item is in your item list:

Please Login or Register  to view this content.

Never seen ISNA before, What is its purpose? or is it just a simple logic test.

Seems to work anyway, many thanks.



Alan

ISNA is a function that checks to see if the result of the VLOOKUP is N/A. Yuo can also use a more generic ISERROR which will check give a "True" value for the VLOOKUP if the item does not exist. Do a search in the Help file for "Is functions" You'll see info for the following:

Please Login or Register  to view this content.

Many Thanks for all your Help, Much appreciated.


Alan

Intergrated the formula into my master spreadsheet today and all seemed to be fine untill someone pointed out that sometimes stock of the same name lives in different cages so may appear twice in the stock list.

I have attached a copy of the revised spreadsheet with the new fomula as above but not quite sure where I can fit into it a line to sum up the stock before returning the value.

Any Ideas...


Alan

Try this (formula is in row 2) = copy down:

Please Login or Register  to view this content.

Using this method will also eliminate the need for the ISNA

Sorry Here is the attachment.

Our posts crossed - please see my previous post.

Thanks, Just tried that and it works great. Just one question on your final SUMIF statement can I ask why you included the titles in the selection. Is this nessersary?


Alan

Ok just answered my own question by fiddling around with it. I see that it is nessersary.

Thanks again.

Alan

Strange if the titles are included I get would I would say is the correct result.

Please Login or Register  to view this content.

However if the titles are removed the results are quite different.

Please Login or Register  to view this content.

What do you make of that. Look at what happens to the remaining qty.

That's why I added this:

Depends on which SUMIF - the last one yes, the first two no to needing the header rows.

unfortunately it was after you received the email notification after my last post without this addition to my post.