The Best Dependent Drop Down Technique

Driven by a gauntlet laid down by friend and fellow MVP Mark Proctor on our Unpivot podcast recently I revisited my earlier "Easiest Dependent Dropdown video" from last year and discovered an EVEN EASIER solution.

The challenge is a combination of making it simple enough that folks can understand, but robust enough that it's then simple / no effort to maintain.

This new solution involves the use of XLOOKUP : XLOOKUP,,,-1 to create the required dependent range. However, this formula isn't accepted inside a validation list so has to be added to a named range. The added benefit of the named range is it can then be referenced simply by the conditional formatting rule too.

In the video I showcase a few techniques which prompted this comment on Bluesky:

Watch now

There are "simpler" techniques where you use TOROW, UNIQUE and FILTER on cells on adjacent rows on the same page and manually copy down / insert as you add / insert new drop down inputs. But it's not as "neat" or as flexible as this new approach in my opinion.

Mark's technique using TAKE and DROP is great and doesn't require named ranges and I realised after recording my video that Mark suggested the XLOOKUP:XLOOOKUP,,,-1 solution at the start of our UNPIVOT podcast and I didn't realise until listening back to the podcast a few days later.

Hope you find this useful. Let me know what you think.

https://accessanalytic.com.au/