SOLVED
Home

Consecutive drop down menu

%3CLINGO-SUB%20id%3D%22lingo-sub-1054547%22%20slang%3D%22en-US%22%3EConsecutive%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054547%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20set%20up%20an%20administration%20in%20Excel.%20In%20the%20sheet%20account%20in%20column%20C%20I%20want%20to%20fill%20in%20categories%20of%20income%20and%20expense.%20In%20column%20D%20of%20the%20sheet%20account%20I%20want%20to%20specify%20column%20C.%20But%20for%20instance%20if%20I%20fill%20in%20the%20category%20groceries%20then%20I%20want%20in%20column%20D%20only%20to%20have%20the%20option%20of%20filling%20in%20the%20shops%20named%20in%20column%20C%20of%20the%20sheet%20tabel.%20What%20kind%20of%20formula%20do%20I%20have%20to%20use%20in%20column%20D%20of%20the%20account%20to%20get%20this%20result.%3C%2FP%3E%3CP%3EI%20attached%20an%20example%20of%20how%20I%20want%20to%20set%20up%20the%20administration.%3C%2FP%3E%3CP%3EAll%20the%20help%20is%20welcome%20and%20I%20thank%20you%20in%20advance.%3C%2FP%3E%3CP%3EBy%20the%20way%20I%20am%20doing%20this%20administration%20in%20Dutch%20because%20that%20is%20my%20native%20language%20so%20I%20have%20to%20translate%20your%20solutions%20but%20that%20is%20no%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1054547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054652%22%20slang%3D%22en-US%22%3ERe%3A%20Consecutive%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F479865%22%20target%3D%22_blank%22%3E%40henk1959%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDutch%20no%20problem%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20workbook.%20It%20uses%20Named%20Ranges%20and%20INDIRECT%20in%20the%20conditional%20formatting%20for%20the%20Description%20dropdown.%20For%20INDIRECT%20to%20work%20I%20changed%20the%20Category%20%22Fixed%20Charges%22%20to%20%22Fixed_Charges%22%20(i.e.%20with%20the%20underscore%20replacing%20the%20space.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055187%22%20slang%3D%22en-US%22%3ERe%3A%20Consecutive%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20quick%20answer.%3C%2FP%3E%3CP%3EI%20get%20the%20impression%20that%20I%20am%20a%20total%20nitwit%20on%20Excel%20now.%20I%20added%20one%20category%20in%20column%20one%20of%20the%20sheet%20tabel%20and%20adjusted%20the%20data%20validation%20in%20sheet%201%20with%20one%20extra%20cel.%20The%20extra%20category%20becomes%20visible%20in%20column%20C%20of%20sheet%20one.%20I%20also%20added%20one%20extra%20store%20in%20column%203%20of%20the%20sheet%20tabel%20for%20the%20category%20groceries%20and%20the%20extra%20category%20from%20column%20one.%20These%20additions%20don't%20become%20visible%20in%20column%20D%20of%20sheet%20one.%20I%20guess%20the%20question%20is%20how%20can%20I%20add%20items%20in%20column%203%20of%20the%20sheet%20%26nbsp%3Btabel%20and%20make%20the%20additions%20visible%20in%20sheet%20one.%3C%2FP%3E%3CP%3EIf%20you%20could%20help%20me%20with%20this%20I%20am%20very%20grateful.%3C%2FP%3E%3CP%3EI%20added%20the%20updated%20excel%20file%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055252%22%20slang%3D%22en-US%22%3ERe%3A%20Consecutive%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F479865%22%20target%3D%22_blank%22%3E%40henk1959%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENever%20too%20old%20to%20learn%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EYou%20figured%20how%20to%20do%20Data%20validation.%20That's%20step%201.%20Now%20you%20need%20to%20understand%20Named%20ranges%20and%20the%20INDIRECT%20function.%20I'm%20on%20a%20Mac%20myself%20and%20don't%20exactly%20know%20how%20the%20screens%20and%20options%20look%20like%20on%20a%20PC.%20But%20it%20shouldn't%20differ%20much.%20On%20the%20Formulas%20ribbon%20you%20should%20find%20a%20button%20Define%20Name%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160652i704E0E1031111A7E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screenshot%202019-12-09%20at%2008.52.09.png%22%20title%3D%22Screenshot%202019-12-09%20at%2008.52.09.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThere%20you%20see%20the%20Names%20I've%20given%20to%20the%20different%20list%20with%20the%20Categories.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160653iCD8152F58D32053A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screenshot%202019-12-09%20at%2008.54.47.png%22%20title%3D%22Screenshot%202019-12-09%20at%2008.54.47.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EJust%20make%20sure%20to%20add%20new%20Named%20ranges%20for%20new%20categories%20or%20expand%20existing%20ones%20if%20you%20add%20options.%3C%2FP%3E%3CP%3EYour%20Description%20drop-down%20points%20to%20a%20list%20in%20an%20absolute%20range%20%22%3DTabel!%24A%242%3A%24A%245%22%20(i.e.%20a%20sheet%20name%20and%20cell%20references).%20The%20drop-down%20for%20the%20Category%20uses%20INDIRECT%20to%20find%20the%20source%20for%20the%20list.%20E.g.%20the%20data%20validation%20source%20list%20in%20column%20D%20points%20to%20%22%3DINDIRECT(C2)%22.%20This%20means%20that%20it%20will%20look%20for%20the%20list%20in%20the%20Range%20with%20the%20Name%20that%20is%20in%20C2%20(i.e.%20Groceries%2C%20being%20C7%3AC10%20on%20%22Tabel%22).%20By%20the%20way%2C%20the%20C2%20automatically%20becomes%20C3%2C%20C4%20when%20you%20go%20down%20in%20column%20D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055294%22%20slang%3D%22en-US%22%3ERE%3A%20Consecutive%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055294%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%20Thanks%20again%20and%20figured%20it%20out.%20Drop%20down%20menu's%20are%20working%20perfect%20now.%20I%20also%20use%20excel%20for%20Mac%20so%20screens%20looks%20the%20same%20%3B-))%3C%2FLINGO-BODY%3E
Highlighted
henk1959
New Contributor

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.

4 Replies
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.

 

@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

@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

Screenshot 2019-12-09 at 08.52.09.png

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

Screenshot 2019-12-09 at 08.54.47.png

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!

 

@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 ;-))
Related Conversations
SharePoint Online Settings menu change?
Richard Bourke in SharePoint on
14 Replies
Megamenu switches back to cascading on a hub site
Beat Grüring in SharePoint on
11 Replies
Feature Request: Add Delete option to Downloads
Eric Hebert in Discussions on
4 Replies
Bug in the Hero Web Part URL?
Philine von Guretzky in SharePoint on
6 Replies
Menu not displaying thumbnails
Jez_Fernandez in Sway on
0 Replies