2
I want to achieve the following.
User selects the item they want in A1 (dropdown menu), called ITEM1
In the second sheet, there are tables with the following properties such as
ITEMS MATERIALS AMOUNT
ITEM1 material1 3
ITEM1 material2 5
ITEM2 material3 1
ITEM3 material4 12
This means that in order to issue an order with ITEM1, the user needs 3xmaterial1 and 5xmaterial2.
However, down the list, some materials need further raw materials to create. For instance
ITEMS MATERIALS AMOUNT
material1 submaterial1-1 4
submaterial1-1 submaterial1-1-1 2
But not all materials have submaterials.
I want to display the total raw materials only.
So when user selects ITEM1 they should get on the same sheet the total amount of raw materials they need:
submaterial1-1-1 x 24
Material2 x 5
I can write a simple VLOOKUP if the item does not have any sub-materials, but i just cannot make it go all the way to the very final raw material, which is the only information my users need to see.
Note: The writing of the tables is not set in stone. I can, if needed, adjust them in any fashion.
Edit: Clarification below as requested. User wants to order "Orange Juice with Bacardi". The raw materials for "Orange Juice with Bacardi" is 2 x Oranges, and 1x Bacardi. Assume that we are producing Bacardi as well. In this case, we would hypothetically need 3 units of Alcohol and 2 units of Sugar.
So the raw material breakdown of an "Orange Juice with Bacardi" is: 2x Oranges 3x alcohol 2x sugar
I want the VLOOKUP table to give me just that. I'm using 2 VLOOKUPs, one for the material and one for the amount of items.
=VLOOKUP($A$1,Materials!$B$2:$D$4, 2, FALSE)
and
=VLOOKUP($A$1,Materials!$B$2:$D$4, 3, FALSE)
But I cannot go "one level down further". Currently I would only get
Oranges 2
Bacardi 1
but I want
Oranges 2
Alcohol 3
Sugar 2
Ikeda Kouji
Posted 2018-03-08T05:43:58.133
Reputation: 21
What exactly you are trying to achieve I'm unable to understand so that editing is also not looking possible. My suggestions are, 1. Edit the post and try to clear your real need. 2. Better include the formula you have tried for the issue, help us to realize your need. – Rajesh S – 2018-03-08T06:33:06.813
Edited the OP for more clarification. Thanks. – Ikeda Kouji – 2018-03-08T07:04:43.777
Do you have the chance to change the tool from Excel to something else. This is a task could be managed much better with a database tool (Access, SQLite, MySQL...) – Máté Juhász – 2018-03-08T08:40:27.370
I was afraid of this.. Unfortunately I don't have any database tools, nor any experience with any of them. – Ikeda Kouji – 2018-03-08T10:28:02.913
1Have you tried nesting this in an IF() statement? You say you have a VLOOKUP() that works for the top level, if you add this to an IF() statement you might be able to solve your problem... IF(submaterial1-1-1,VLOOKUP(),(IF(submaterial1-1(VLOOKUP(),VLOOKUP()))). – Kevin Anthony Oppegaard Rose – 2018-03-08T15:15:50.687
@Kevin's approach is the way to go. The formula will get quite long depending on how many levels of sub-materials there are. It sounds like there can be 3 levels: materials, sub-materials, and sub-sub-materials. Is that correct? – Bandersnatch – 2018-03-08T15:42:10.320
I have 3 columns, but the sub-materials go as deep as 14 to 15 levels. Unfortunately I could not get a working IF + VLOOKUP due to the following reason:
All my data is laid out in 3 columns. This is because some materials are repeating in both columns, and since there are 14 to 15 levels.
For instance,
`Mat1 Mat2 Amount
Laminated Beam Oak 3
Composite Beam Laminated Beam 2
Strong Beam Laminated Beam 5
Strong Composite Beam Strong Beam 5`
I just want the result to give me "375 Oak" when user selects Ironhide Beam. – Ikeda Kouji – 2018-03-13T02:36:44.760
Can you post 2 lines of sample data and add an expected result to both lines? – Kevin Anthony Oppegaard Rose – 2018-03-18T06:57:23.247