autosum/ auto calculate each item from a random list of items

**angerion** · 12-26-2013, 12:24 PM

Hi,
I have a list of sales order, each order require specific material(and size).
in order to control the stock of each material(and size), i need a formula or VBA that can autosum/ auto calculate sales order that use same material(and size).

Please check my attachment.

If possible, the formula/ VBA can works even if the 'stock' and 'sales order' sheet is on different workbook (different computer connected by Local Area Network, because the person who control stock is not the same person who control sales order, all this time we're controlling it manually)

thanks.

**newdoverman** · 12-26-2013, 09:20 PM

I assigned a Serial # to each product as well as I could on both worksheets. Then I applied the SUMIFS function to find out how much usage was on the Sales Order Page that wasn't identified as DONE.

**Max, Singapore** · 12-26-2013, 09:32 PM

One way ...
In stock,
Place in G7, normal ENTER will do:
=IFERROR(INDEX('sales order'!$F$4:$F$10,MATCH(1,INDEX(('sales order'!$C$4:$C$10=C7)*('sales order'!$D$4:$D$10=D7)*('sales order'!$E$4:$E$10=E7)*('sales order'!$G$4:$G$10<>"done"),),0)),"")
Copy down

p/s: Its best to keep all calcs in the same file, eg Move the sheet: stock into the file where you have 'sales order' (or vice versa)
-----------------------------------------------------------
Works ok? Wave it, whack the little star at the bottom left of my responses

**angerion** · 12-28-2013, 03:30 AM

thanks guys, thank you for the fast reply..

Max, your formula can work, but when the Usage is empty, Final Qnty goes #VALUE!

Newdoverman, yours is working perfectly, but here comes another problem:
If only half of the order is done, how to make the formula?
I suppose it shall be the change of criteria2 --> "" or plus/minus 'status', but i don't know how to make the formula.
And, is it possible to make this work if 'stock' sheet and 'sales order' sheet is in different computer?

thank you again.
angerion

**newdoverman** · 12-28-2013, 11:04 AM

It is a matter of inserting the links to the other files on the other computers and the other files have to be open when accessed.

Your notation of expecting 6 as the result for Product A is a surprise as you don't mention that both entries for Product A is the same transaction to be treated together. The same would the hold true for Product D which would be 1 instead of 6. This is easily fixed in a small data set if but how can one separate the transactions when there is no indication other than product name as to what belongs with what? If it is a matter of how much product was used total regardless of sales total then that could be worked around.

It also appears that Product C and Product D are the same product (same dimensions) which cannot be distinguished on the Stock worksheet. I made a change (for calculation purposes and this example in the width of the 920 product to 980). So that the different products would show their own values.

**angerion** · 12-30-2013, 06:08 AM

sorry, my bad, i don't explain it well..

both entries for Product A is NOT the same transaction, the second one is repeat order.
the scenario is, 1st entry(first transaction) of Product A is done, the 2nd entry(transaction) is half done.
i've change my example, please check the attachment below.
the change is,
1st entry is done.
2nd entry only 3 is done, 7 isn't.

also, note that it is a printing production, there is a possibility that 2 or more products use/ share the same material size (only the design/ plate is different, not use in calculation)

the main purpose of this is just to control the stock. i only need the total usage of each material(and size) to show in 'usage'column in 'stock'sheet, no need to divide each product specifically.
I suppose it shall be the change of criteria2 in that =SUMIFS --> "" or plus/minus 'status', but i don't know how to make the formula.

Sorry for my bad english, hopefully i explain it well this time.

thanks,
angerion

**newdoverman** · 12-30-2013, 10:10 AM

There is no way to calculate this as it stands. The numbers that you are show as being used have no origin on your worksheet or workbook.

**angerion** · 12-30-2013, 11:07 PM

unfortunately, looks like i have to cut the quantity of sales order manually until its done...
thank you for helping me, i have add your reputation..

thanks again,
angerion

**newdoverman** · 12-30-2013, 11:23 PM

Thank you for the feedback.

I think that you may have more success if you allocated your materials via order rather than product. That will give you control over how the materials are used or should I say, how they were used.

Good luck with your project and thank you for the reputation.

autosum/ auto calculate each item from a random list of items

LinkBack

Thread Tools

Rate This Thread

Display

autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Re: autosum/ auto calculate each item from a random list of items

Thread Information

Users Browsing this Thread

Similar Threads

Selecting random samples from list of items without duplication

How to auto determine MAX and MIN for each item from total items ?

[SOLVED] Items in Userform Listbox are wider than list box. Need to see all character of item

Pivot Table dropping items from page item list

Random items from a list?

Bookmarks

Bookmarks

Posting Permissions