Pulldown List from Dependency Table

[Alhelor]

Hey folks,

imagine this production dependency table:

Producing a car requires a computer. Producing a Dog requires a Car and a House. Producing Your Mom or a Cake doesn't require anything, they're given, etc.

Now the task is to put the content into a table looking like this:

Since the Dog requires two ingredients, it comes up twice. Neither Your Mom nor Cake are part of the list as we don't know their ingredients.

What would be the pull-down formulas for "Produced" and "Consumed"?

Have a great day!

 

[Eric W]

Definitely tricky. This is trending toward a VBA solution, but it can be done with formulas as follows:

	A	B	C	D	E	F	G	H	I	J	K	L
1st degree dependencies	Produced	Consumed
Car	Computer
Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake	Dog	Car
Car	x	Dog	House
Dog	x	x	House	Cake
House	x	Computer	Your Mom
Computer	x	Wizard	Your Mom
Your Mom
Wizard	x
Cake

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #757171, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K3[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$5:$A$11,SMALL(IF($B$5:$H$11="x",ROW($B$5:$H$11)-ROW($B$5)+1),ROWS($K$3:$K3))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$4:$H$4,SMALL(IF(SUBTOTAL(3,OFFSET($B$5,MATCH(K3,$A$5:$A$11,0)-1,COLUMN($B$5:$H$5)-COLUMN($B$5))),COLUMN($B$5:$H$5)-COLUMN($B$5)+1),COUNTIF($K$3:$K3,K3))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I put the table in K2:L9, you can obviously move it if you need to. Both are array formulas, enter them with Control+Shift+Enter, then drag down.

Hope this helps!

 

[Eric W]

Here's a bit simpler version of the L3 formula:

=IFERROR(INDEX($B$4:$H$4,SMALL(IF(OFFSET($B$5:$H$5,MATCH(K3,$A$5:$A$11,0)-1,0)="x",COLUMN($B$5:$H$5)-COLUMN($B$5)+1),COUNTIF($K$3:$K3,K3))),"")
with CSE