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
1
2	1st degree dependencies
3
4	Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
5	Car				x
6	Dog	x		x
7	House							x
8	Computer					x
9	Your Mom
10	Wizard					x
11	Cake
12
13
14	2nd degree dependencies
15
16	Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
17	Car					x
18	Dog				x			x
19	House
20	Computer
21	Your Mom
22	Wizard
23	Cake
24
25
26	3rd degree dependencies
27
28	Green consumes Red	Car	Dog	House	Computer	Your Mom	Wizard	Cake
29	Car
30	Dog					x
31	House
32	Computer
33	Your Mom
34	Wizard
35	Cake

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B17 {=IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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:

Cell Formula B17 {=IF($A17=B$16,"",(IF(B5="x","",IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")))} <tbody> </tbody>

<tbody>
</tbody>

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!