Smarter alternative to Name Manager & Indirect Formula?

[RockandGrohl]

Hello guys, I'm plumbed the depths of ChatGPT knowledge and it couldn't help me out, so I'm going traditional.


Please see below for our data structure.

The data (Asset) is categorised under two headings. First is Cost Heading, second is Cost Group.

On another sheet, the user first selects the Cost Heading (Frozen Food), then the Cost Groups which are only applicable to Frozen Food - (Pies) and then finally the Asset which is only applicable to Pies - Vegetable (yuck)
They can do this for any number of lines, and each line is indepedent of the others so you can go Frozen Food - Pies - Vegetable on one line, Fresh Fruit - Small Fruit - Cherry on the other, etc.

Currently this is being done by having a Data Validation list of all the unique Cost Headings, and then using Indirect formula in combination with Name Manager to pull only the appropriate names out.

This has a number of disadvantages:

1. We can't alphabetise the list if things are added to the structure haphazardly.
2. This list of assets sits on a master sheet and copies are added to various workbooks we have. When you copy the master sheet, the Name Manager references change from Global to Local. I am the only one in the company with a plugin to change this back, and it's a ballache of a process
3. The rules to get things working in Name Manager are frustrating and errors are frequently made when new Assets are added or things are altered.

I'd like to move away from this if possible, and have seen guides like this that promise to work:

Unfortuntely it seems like if you do more than two groups of dropdowns, it stops working. As I said, one of the requirements is that there will be numerous rows where the user can select the asset they want and this means each dropdown must be independent.


If you guys can think of anything, please let me know.

 

[RoryA]

Since you're on 365, see if Mynda's latest blog post helps: Multi-level Dependent Drop-down Lists