Consecutive drop down menu

[henk1959]

I am trying to set up an administration in Excel. In the sheet account in column C I want to fill in categories of income and expense. In column D of the sheet account I want to specify column C. But for instance if I fill in the category groceries then I want in column D only to have the option of filling in the shops named in column C of the sheet tabel. What kind of formula do I have to use in column D of the account to get this result.

I attached an example of how I want to set up the administration.

All the help is welcome and I thank you in advance.

By the way I am doing this administration in Dutch because that is my native language so I have to translate your solutions but that is no problem.

[#2 · Accepted solution]

@henk1959 

Dutch no problem :)

See attached workbook. It uses Named Ranges and INDIRECT in the conditional formatting for the Description dropdown. For INDIRECT to work I changed the Category "Fixed Charges" to "Fixed_Charges" (i.e. with the underscore replacing the space.

[#3]

@Riny_van_Eekelen 

Thank you for your quick answer.

I get the impression that I am a total nitwit on Excel now. I added one category in column one of the sheet tabel and adjusted the data validation in sheet 1 with one extra cel. The extra category becomes visible in column C of sheet one. I also added one extra store in column 3 of the sheet tabel for the category groceries and the extra category from column one. These additions don't become visible in column D of sheet one. I guess the question is how can I add items in column 3 of the sheet  tabel and make the additions visible in sheet one.

If you could help me with this I am very grateful.

I added the updated excel file again

[#4]

@henk1959 

Never too old to learn :)

You figured how to do Data validation. That's step 1. Now you need to understand Named ranges and the INDIRECT function. I'm on a Mac myself and don't exactly know how the screens and options look like on a PC. But it shouldn't differ much. On the Formulas ribbon you should find a button Define Name

There you see the Names I've given to the different list with the Categories.

Just make sure to add new Named ranges for new categories or expand existing ones if you add options.

Your Description drop-down points to a list in an absolute range "=Tabel!$A$2:$A$5" (i.e. a sheet name and cell references). The drop-down for the Category uses INDIRECT to find the source for the list. E.g. the data validation source list in column D points to "=INDIRECT(C2)". This means that it will look for the list in the Range with the Name that is in C2 (i.e. Groceries, being C7:C10 on "Tabel"). By the way, the C2 automatically becomes C3, C4 when you go down in column D.

Hope this helps!

[#5]

@Riny_van_Eekelen, Thanks again and figured it out. Drop down menu's are working perfect now. I also use excel for Mac so screens looks the same ;-))