Sumproduct criteria problem

[john dalton]

Hi,

I have a sheet that requires stock to be exhausted from one source before it is taken from another source, it depends on the criteria of product number & items taken against items available.

I have attached the spreadsheet for you to look at.

Cheers

JD

[quekbc]

Hi John Dalton,

What you need is a MIN function under capex. I.e. take the minimum of your balance + inflow against outflow.

Using the workbook you've provided change the formula to...

Please Login or Register  to view this content.

Hope this helps.

[john dalton]

That works brilliantly - the problem that occurs now is that when capex stock is delivered no matter that stock is now being taken from the opex source stock.

It adjusts the opex stock out stock accordingly instead of just adding to the capex stock. In c15 if you type C1234, DEL in D15, 1 in E15 and 001 in F15 you will see the result , is there any way of stopping this from happening?

JD

[quekbc]

Hi John,

Isn't the intention to take stocks from from opex if capex is unable to support it?

[john dalton]

Yes, however, we also buy Capex stock from time to time and when it entered into the stock sheet I need the opex stock to be unaffected by the new incoming stock as it is now - hope that explains it.

JD

[quekbc]

Hi John,

I am unable to replicate the issue as I think the data you have and the data I have is different (i dont have data in row 13 and 14). Can you post a new workbook for me to have a look at?

[john dalton]

Here you go

[quekbc]

Ah, I see the problem. At this moment, I do not have a 1 cell formula solution. What I'd envisage doing would be to recalculate the stock balance for opex and capex for every data input you put in. That way you get a running balance for each data input and you can use that running balance to keep track.

See attached for example.

[john dalton]

Great solution, however I have 2000 linees of stock ranging from 001 to 2000 is there any way to compress the solution to allow for that amount of stock?

[quekbc]

Thanks John,

001 to 2000. That's a LOT of stock lines. I can only think of three solutions.

1) Use the solution but repeated 2000 times (pain but dynamic)
2) Use a data table to generate key numbers that you want. (SLOW calculations, semi-dynamic)
3) Use macro to do point 2 above (Faster calculations, not dynamic).

[john dalton]

I have managed to include the data table into the stock movement sheet as to limit the amount of data tables required - however I cannot seem to get it work correctly - please find attached sheet.

JD

[quekbc]

Hi John,

I'm not too sure what you're trying to achieve here. Can you clarify more on this please?

[john dalton]

I am looking to run the dynamic data table next to the stock movement sheet - as each item is taken or delivered the data table updates the stock accordingly - I can get it to work for 1 line but when further entries are put into the stock movement sheet the data table must be flexible enough to log the last stock aty and then when that particular stock ID no. is used again it can then update it to the new qty.

Hope that clarifies it a bit.

JD

[john dalton]

I am trying to get a flexible dynamic table so that I dont have to create 2000 seperate tables for each product as each table would need a min 150 lines to cover each months stock movement.

JD

[quekbc]

Hi John,

I think you've misunderstoond what a data table is.

See here for more details on data table. http://office.microsoft.com/en-us/ex...010072656.aspx

I've attached a spreadsheet with a data table in the previous spreadsheet we worked on before.

[john dalton]

Right will have a look, cheers pal.

JD

[john dalton]

ok any chance of talking me through how you produced the attached sheet please.

Cheers

JD

[john dalton]

hi, can you tell me how you did it.

Cheers

JD

[quekbc]

Sure think John. Using the references on the spreadsheet.

B9:E38 = Inputs for stock coming in or out.
B2:D4 = Initial opening balances for 001 002 and 003.
G8 = Change this to 001, 002, or 003 to calculate the stock inflow/outlow for each product ID number.

Key outputs that (I'm assuming) you want to see is in H3:Q3.

From what I understand, you have a long list of orders for different ID numbers and you want a summary of total inflow, total outflow, and the balances. Now, changing the value in G8 will calculate the total inflows/outflows/balances for any product ID you put in G8. However, doing so you only get 1 output for 1 prod ID.

If all that really matters is the Prod ID and the key outputs, you can use a data table as per on rows 41 to 47. Now, what data table does is it takes the numbers in B45:B47, puts it in G8 one at a time, calculates, and pastes the output right next to the Prod ID numbers in B45:B47. So, for example, for ID No. 003, the Inflow/Outflow for Opex is 2/2 and for Capex is 3/3. You get these exact same numbers if you manually type in 003 in G8. Data table does it automatically.

To create the data table, list down all Prod IDs (in my example, just 001 002 and 003) in one column - B45:B47. On the row just above the first column input (001) and one column to the right of it, list down, in 1 row, the outputs you want to see. This can be seen in cells C44:L44. Select the whole box (i.e. cells B44:L47, go under Data --> Data Table. A small box will pop up, under COLUMN input cell, select cell G8 and click OK. This will generate the table. You might have to press F9 to update the table.

[john dalton]

brilliant, OK hopefully you have an answer to this - the entry in cell G8 that activates the sheet is it possible to have this entry linked to the stock coming in or out sheet - say the last entry as each line is entered?

Cheers

JD

[quekbc]

As in, if the last entry is Prod ID 999, G8 will show 999?

[john dalton]

as in the last current entry in the stock in/out list so say row no.48 was product id 001 then g8 would show 001, then if row no. 49 was product 003 then g8 would show 003 or is this not necessary? does the data table automatically update it anyway?

Cheers

JD

[quekbc]

It's not necessary. The data table will automatically "change" G8 and record the associated outputs.

[john dalton]

Cheers quekbc will integrate it into my stock sheet and let you know how I get on

JD