Finding disabled parents in a menu hierarchy

[alexdi]

I have a permissions sheet that describes a set of hierarchical menus. The 'group' columns correspond to whether that group can access that menu.

parentfind.png

It's possible to enter a "Y" for a child even if the parent is disabled with an "N". That's a problem; the child won't be accessible if the parent is disabled.

I'm trying to create a column that reports, for an enabled child, for which groups the child's parent is disabled.

I have a formula that would work, but it's entirely hard-coded with approximately 150 tests (4 layers x ~40 groups). It's essentially a VLOOKUP on a query table (not shown) that reports all item / "N" combinations. Excel won't let me use it because it's too long.

Power Query and VBA are both acceptable solutions for this.

Any thoughts? Much thanks.

[GlennUK]

Have a look at this ... it uses columns to the right to do the calculations:

[alexdi]

Thanks Glenn! I ended up with a similar solution, essentially splitting my hard-coded formula in a set of helper columns. I couldn't figure out how to get it all into one column. I like your syntax though, I'll be adapting some of it. Thanks again!

[GlennUK]

My pleasure. The added advantage of my extra columns is that you can link Conditional Formatting to them, and then highlight the problem cells in red ... as in the example there.