Logic Formulas for Dependency Tables

[Alhelor]

Hey folks,

Take a look at these three dependency tables:

Let's take Dog as an example. In order to produce a Dog, you need to consume a Car and a House (1st degree dependency, first table). However, in order to produce a Car, you need to consume a Computer, and in order to produce a House, you need to consume a Cake. You can see it in the first table. However, it makes the Dog also dependent on a Computer and a Cake in 2nd degree (2nd table) because without those there's no Car and no House.

Now, Cake has no dependencies in the first table, so it can be assumed given. However, producing a Computer requires Your Mom. Thus, the Dog is dependent on Your Mom in 3rd degree (3rd table).

My question: What would the formulas in table 2 and 3 look like? Which functions to use?

If something is dependent on itself, return "".

Thank you!

 

[Eric W]

It's essentially the same formula for both tables:

	A	B	C	D	E	F	G	H
1st degree dependencies
Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
Car	x
Dog	x	x
House	x
Computer	x
Your Mom
Wizard	x
Cake
2nd degree dependencies
Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
Car				x
Dog			x			x
House
Computer
Your Mom
Wizard
Cake
3rd degree dependencies
Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
Car
Dog				x
House
Computer
Your Mom
Wizard
Cake

<colgroup><col style="width: 25pxpx"><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: 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: 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: center"]4[/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: 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: 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: 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: 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: 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: 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: 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: center"]13[/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"]14[/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"]15[/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"]16[/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]24[/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"]25[/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"]26[/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"]27[/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"]28[/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

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

[TD="bgcolor: #757171"][/TD]

</tbody>

Sheet4​

[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"]B17[/TH]
[TD="align: left"]{=IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")}[/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]



Put the formula in B17, confirm with Control+Shift+Enter. Then copy to B17:H23 and B29:H35.

It does rely on the gap between tables being the same. If you change the ranges, you may need to adjust the formula within each table to match the proper ranges.

Hope this helps!

 

[Marcelo Branco]

Eric

Wow...very nice!

M.

 

[Alhelor]

@Eric

This is beyond impressive! Thanks a lot!!!!!

 

[Eric W]

Glad I could help!

Marcelo, Rada1, and Alhelor, thanks for the likes! I have to admit, I'm pretty happy with how that turned out. After I got it working, it simplified really nice.

 

[Alhelor]

Glad I could help!

Marcelo, Rada1, and Alhelor, thanks for the likes! I have to admit, I'm pretty happy with how that turned out. After I got it working, it simplified really nice.

I slightly adjusted your formula to 1. avoid dependencies on oneself and 2. avoid multiple degree dependencies at once. It now looks like this:

[TABLE="width: 1078"]
<tbody>[TR]
[TD][TABLE="width: 1070"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=IF($A17=B$16,"",(IF(B5="x","",IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I posted another thread for a (much more complex) continuation https://www.mrexcel.com/forum/excel...ons-dynamic-dependency-table.html#post4848271. Feel free to check it out!