Dependent drop down lists

%3CLINGO-SUB%20id%3D%22lingo-sub-1476624%22%20slang%3D%22en-US%22%3EDependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1476624%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20create%20a%20drop%20down%20list%20with%20multiple%20words%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1476624%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1476751%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1476751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704496%22%20target%3D%22_blank%22%3E%40LA_Mitche11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESet%20up%20a%20list%20of%20words%20in%20a%20column%20on%20a%20separate%20sheet.%3C%2FP%3E%3CP%3EThen%20place%20the%20cursor%20on%20the%20cell%20that%20you%20want%20to%20create%20your%20drop%20down%20-%20and%20select%20Data%20Validation%20from%20the%20Data%20menu.%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20ALLOW%20option%20select%20LIST.%3C%2FP%3E%3CP%3EIn%20the%20source%20select%20the%20range%20where%20your%20words%20are.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20a%20basic%20method.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20other%20things%20you%20can%20do%20to%20make%20it%20slicker%20but%20try%20that%20first.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1476763%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1476763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYeas%20I%20am%20fairly%20competent%20with%20that%20basic%20operation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20created%20that%20I%20would%20like%20to%20have%20the%20adjacent%20cell%20display%20a%20list%20of%20comments%20relating%20to%20that%20selected%20on%20its%20left.%3C%2FP%3E%3CP%3Ehave%20tried%20%3Dindex%20and%20%3Dindirect%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1477169%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1477169%22%20slang%3D%22en-US%22%3EDo%20you%20mean%20you%20want%20one%20drop%20list%20with%20(say)%20A%20B%20C%20and%20a%20second%20drop%20down%20which%20would%20have%20A1%2CA2%2CA3%20if%20A%20is%20selected%3B%20then%20B1%2CB2%2CB3%20if%20B%20I%E2%80%99d%20selected%20and%20do%20on%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480830%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BYes%20that%20is%20what%20I%20meant!%26nbsp%3B%20Worked%20my%20way%20round%20it%20now%20though%20I%20think.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELesleyanne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1481138%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704496%22%20target%3D%22_blank%22%3E%40LA_Mitche11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok%20fair%20enough%20-%20but%20i%20think%20there%20is%20a%20solution%20available%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1519997%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704496%22%20target%3D%22_blank%22%3E%40LA_Mitche11%3C%2FA%3E%26nbsp%3BWere%20you%20able%20to%20make%20a%20dependent%20drop%20down%3F%20If%20so%20could%20you%20share%20the%20steps.%20I%20think%20I%20have%20tried%20the%20same%20steps%20you%20mentioned.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1520139%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727509%22%20target%3D%22_blank%22%3E%40GBROOKS1953%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F704496%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40LA_Mitche11%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attaching%20a%20method%20of%20what%20I%20called%20%22cascading%22%20drop%20down%20or%20data%20validation%20lists.%20This%20simple%20demo%20is%20just%20based%20on%20first%20and%20last%20names....you%20can%20add%20combinations%20of%20first%20and%20last%20names%20to%20test%20the%20process.%20When%20you%20do%2C%20you'll%20notice%20that%3C%2FP%3E%3CUL%3E%3CLI%3Ethe%20list%20of%20unique%20first%20names%20grows%20as%20a%20new%20first%20name%20is%20added%3C%2FLI%3E%3CLI%3Ethat%20list%20of%20unique%20first%20names%20is%20the%20basis%20for%20the%20drop%20downs%20in%20the%20first%20box%2C%20where%20you%20are%20invited%20to%20select%20a%20first%20name.%20That%20drop%20down%20list%20grows%20with%20each%20additional%20unique%20first%20name%3C%2FLI%3E%3CLI%3EOnce%20you've%20selected%20a%20first%20name%2C%20the%20list%20of%20unique%20last%20names%20that%20correspond%20to%20the%20selected%20first%20name%20is%20displayed%20and%20serves%20as%20the%20data%20validation%20list%20for%20the%20second%20drop%20down%20box.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThis%20method%20makes%20use%20of%20the%20new%20Dynamic%20Array%20functions%2C%20%3CSTRONG%3EUNIQUE%3C%2FSTRONG%3E%2C%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20in%20particular.%20I%20also%20employ%20%3CSTRONG%3ESORT%3C%2FSTRONG%3E%20so%20that%20the%20lists%20are%20shown%20in%20alphabetical%20order.%20But%20if%20you%20don't%20have%20the%20most%20recent%20version%20of%20Excel%2C%20you%20will%20not%20be%20able%20to%20use%20those%20functions%2C%20and%20therefore%20this%20method%20will%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I would like to create a drop down list with multiple words

 

7 Replies
Highlighted

@LA_Mitche11 

Hi

 

Set up a list of words in a column on a separate sheet.

Then place the cursor on the cell that you want to create your drop down - and select Data Validation from the Data menu. 

On the ALLOW option select LIST.

In the source select the range where your words are.

 

That is a basic method. 

 

There are other things you can do to make it slicker but try that first.

 

Peter

 

 

Highlighted

@peteryac60 

Yeas I am fairly competent with that basic operation.

 

Having created that I would like to have the adjacent cell display a list of comments relating to that selected on its left.

have tried =index and =indirect

 

 

Highlighted
Do you mean you want one drop list with (say) A B C and a second drop down which would have A1,A2,A3 if A is selected; then B1,B2,B3 if B I’d selected and do on?
Highlighted

@peteryac60   Yes that is what I meant!  Worked my way round it now though I think.

 

Lesleyanne

Highlighted

@LA_Mitche11 

 

ok fair enough - but i think there is a solution available 

@LA_Mitche11 Were you able to make a dependent drop down? If so could you share the steps. I think I have tried the same steps you mentioned.

Highlighted

@GBROOKS1953 

@LA_Mitche11

 

I'm attaching a method of what I called "cascading" drop down or data validation lists. This simple demo is just based on first and last names....you can add combinations of first and last names to test the process. When you do, you'll notice that

  • the list of unique first names grows as a new first name is added
  • that list of unique first names is the basis for the drop downs in the first box, where you are invited to select a first name. That drop down list grows with each additional unique first name
  • Once you've selected a first name, the list of unique last names that correspond to the selected first name is displayed and serves as the data validation list for the second drop down box.

This method makes use of the new Dynamic Array functions, UNIQUE, FILTER in particular. I also employ SORT so that the lists are shown in alphabetical order. But if you don't have the most recent version of Excel, you will not be able to use those functions, and therefore this method will not work.