Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

creating multiple dependent drop down menus in Excel

0

Hello, I have watched several of the tutorials in YouTube however the Data Validation 4 - Create Multiple Dependent Drop Down Menus With Complex Names in Excel will not play. I am trying to create dropdown lists that are dependant on what is chosen in a separate dropdown menu.

For example if Utilities is selected from Drop Down menu A, then drop down menu B will provide Gas, Water, or Hydro. If Software is selected in Drop Down Menu A, then drop down menu B will provide EMR, Training, or Programs. If Programs is selected from drop down menu B then drop down menu C will provide License, Subscription, or Purchased. If Premises - Other is selected from Drop Down menu A, then drop down menu B will provide Property Tax, Staffing, City Costs, or Other.

Drop down menu C should only allow choices if drop down menu B has Programs selected.

Do the drop down menu B's have to contain the same number of options within the table?

Thank you for your time and consideration of this question

excel
MS Office Version: Office 365
posted by: JColwell (rep: 2) on Fri Nov 26, 2021 at 12:01 am
Answer
Discuss

Discussion

Hi and welcome to the Forum

What's your question please? Just about the number of options in each dropdown?

I found the tutorial (created by Don) you refer to but it's one which has to be paid for. If you've attempted to create your own version, you can attach that Excel file by editting your original question and using the Add Files... button.
John_Ru (rep: 6142) Nov 26, '21 at 3:39 am
Hi John,
Thank you for your response. There are really two questions I have.
1. Do the columns in the table need to contain the same number of options?
2. Is there a way to only show drop downs options from Programs SSC in the Expense Sub Catagory dropdown if Programs is selected from the Expense Catagory dropdown menu?
What I am trying to do is make Excel into a database for all invoicing but be able to breakdown the expenses into sub catagories and sub sub catagories as needed for data analysis on expenses.
Thank you again for your time and consideration of my question
JColwell (rep: 2) Nov 29, '21 at 1:24 pm
J

I'll look at my Answer file on a bit but creating an Exvel database for invoicing is probably "re-inventing the wheel" and your company might be better served by a commercial package (rather than a home-grown spreadsheet) 

Also, if your comments above relate to my Answer, next time please add them to the Discussion under the Answer (not under the Question) 
John_Ru (rep: 6142) Nov 29, '21 at 1:45 pm
Add to Discussion

Answers

0
Selected Answer

J

In the attached file, I've used the method shown in another of Don's tutorials Automatically Updating Dependent Drop Down List Menus in Excel to create the file you describe (hopefully).

You'll see a new, separate table headed "Programs" (a named range ProgramsInfo) which is used for data validation in cell E2 which appears blank normally but goes grey when Menu B (cell C2) is set to Programs (and Menu C appears above it).

The formula in E1 is just:

=IF(C2=D8,"Menu C","")
and in E2, the List data validation formula is:
=INDIRECT("ProgramsInfo["&C2&"]")
and the conditional formatting formula for the cell fill is:
=IF($C$2=$D$8,1,0)
where the parameter 1 is like  logical  True so the set formatting appears when C2 is the right value (i.e. Programs in cell D8- shaded green just to illustrate it's different).

Hope this works well for you.

posted by: John_Ru (rep: 6142) on Sat Nov 27, 2021 at 5:52 am
Discuss

Discussion

Thank you!!
JColwell (rep: 2) Nov 29, '21 at 2:12 pm
J. Thanks for selecting my answer. There may be a better way (given you have Excel365) eliminating blanks and sorting list, based on another of Don's tutorials: Data Validation List with Dynamic Arrays in Excel but I don't have time to work it out, sorry.
John_Ru (rep: 6142) Nov 29, '21 at 3:26 pm
I found the answer to my second question originally posted - apologies for adding the post!!
JColwell (rep: 2) Nov 30, '21 at 12:07 pm
Okay, I assume you're referring to the Discussion point mentioning the use of IFS and named ranges (which presumbaly you deleted since it had gone my the time I booted up my PC!)
John_Ru (rep: 6142) Nov 30, '21 at 12:43 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login