Forum Discussion
Consecutive drop down menu
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.
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
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!