Dependent Drop Down Lists for Categories and Subcategories

[heretolearnexcel]

Hello,

I have the following table of categories and subcategories to classify expenses:

Category	Subcategory
Entertainment	Entertainment
Education	Books
Savings	Emergency Fund
Car	Fuel
Loans (as Loaner)	Loans to Friends
Loan (as Loanee)	Car Loan
Charity	Friends
Health	Therapy
Personal Care	Skin Care
Personal Care	Hygiene
Health	Medication
Car	Accesories
Utilities	Cell Phone
Groceries
Gifts
Other
Clothing
Financial Services	Credit Card Interest Charges
Financial Services	Credit Card Commission
Professional Development
Health	Other

I register the expenses in the following table:

Date	Category	Subcategory	Description	Amount
1-Aug-21	Gifts	Entertainment		$10.00
6-Aug-21	Groceries	Entertainment		$20.00
8-Aug-21	Health	Entertainment		$25.00

I'm looking for a way to have a drop-down for "category" and "subcategory" in each row, and be able to select a category, and only display and select the subcategories that correspond to that category. I tried using various methods with the FILTER function, but they don't work because the relationship stays fixed, it doesn't move along with the rows.

I'd appreciate any suggestions, thanks in advance.

 

[BigBeachBananas]

Try this:

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[heretolearnexcel]

So, I suppose I'll have to create a separate list for each category and subcategory? I was hoping I could find a solution with only the original list or one for category and another for subcategory, at the most.

 

[BigBeachBananas]

So, I suppose I'll have to create a separate list for each category and subcategory? I was hoping I could find a solution with only the original list or one for category and another for subcategory, at the most.

Are there more than one sub-category within one category?

 

[heretolearnexcel]

Are there more than one sub-category within one category?

Yes, and the list will probably grow.

 

[heretolearnexcel]

Update:

I found this method, which seems to make the process of updating the data simpler: How to Make Excel Dependent Drop Down Lists With Tables

 

[heretolearnexcel]

Update:

I found this method, which seems to make the process of updating the data simpler: How to Make Excel Dependent Drop Down Lists With Tables

Here's a video explaining each step, in case it's helpful for anybody who bumps into this thread:

 

[Akuini]

Another option:
if you have 3 or more dependent data validation then it might be difficult to set it up as it requires lots of tables & lots of helper columns. I created a version that uses vba, you only need 1 table, 1 helper column & 1 named range. The code is a bit complicated but easy to set up and maintain. Here:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

 

[heretolearnexcel]

Thanks for the contribution! I'm sure that'll be helpful to many people.