Dynamic Drop Down with Multiple Criteria

I am trying to create a time tracking sheet. We want the user to be able to select project type, category, and sub-category. This is for an audit group.

We would want the time sheet to have 3 drop down lists to mirror the options available. The trick is that we are trying to make each dynamic based on the columns selected. I've found some examples using named ranges, but it looks like to accomplish the categories we have in out sheet I would have to make a lot of named ranges and that could be difficult to understand and manage for someone else.

I would think you'd be able to create type of dynamic match that you use to create a data validation list, but I'm really not sure exactly how to do that.

Any ideas?

At a high level the category tab looks like this

I've attached a file the one tab houses our Project Type, Category, and Sub-Category. The next types has a timesheet which we would like to have 3 dynamic drop down lists and a calendar to the right.

Let me know if I need to be more clear.


Open Excel Online Link if you don't want to download:

1drv.ms/x/s!AnMRr0Q8-EGOgqxY2-MsE94uXSj6cw?e=lAuR5n

It looks like these examples primarily use named ranges with two criteria. I'm specifically trying to avoid that since that means I have to manage approx 170 named ranges to make mine work like the examples because that's the number of categories I have for sub-categories. There really isn't a way for excel for a formula to evaluate matches in a table without setting up a named range for each higher level category.

Awesome Glen!

I have an example of 2 dependent data validation. It's using VBA, need 1 helper column & only 1 named range.
If you're interested in this approach I can set up for 3 data validation as you need.
Here's an example:

Glen, I'm busy the next few hours, but I'll take a look at it. Thanks

popipipo, unfortunately this won't work like I would like, because my subcategories will change. I know my example file wasn't great because I only had the 3 type, but I need to be able to have add a new unique set of 3 values and the data validation list will update for it.

Glen the formulas in the helper columns and the formula in the data validations lists are really hard for me to understand.

I did try to make it work like I thought it was intended. I changed on of the existing subcategories and it. I also added a new combination of 3 values and extended the helper columns. This one worked as expected for Type, Category, but didn't show the subcategory.

I've attached mine that I changed. I also created a tab called "What I Changed" so you can see what I changed and what I was seeing.

Here's an example of 3 dependent data validation. It's using VBA (not formula), need 1 helper column & only 1 named range.
The code is rather long but is pretty easy set it up, even if you don't understand the code.
Here's the sample workbook & your workbook using this set up.

This is wonderful. Thank you so much!