Forum Discussion
How to Populate a List from a Drop Down List?
For my job I am trying to make a sheet so based on what you select in the drop down list, a list of qualities will appear under it. For example, if my boss selected Leadership Competencies from the drop down menu, then the list of qualities would appear based on what is listed in another sheet. I am doing this to make it easier to compare qualities. I am not sure if HLOOKUP or VLOOKUP are the best option since they usually only produce one piece of data instead of a whole list. Any ideas on what to do?
Try the solution in the attached workbook, leaving most of what you already made intact. It's perhaps not the most elegant, but it's simple and it works.
1. I created Named Ranges of your competencies in Overall List.
2. I replaced all spaces in list that populates your dropdown boxes on the Comparisons sheet. This is needed to make the options exactly the same as the names created in step 1. It is needed to make the step 4 work.
3. I inserted a helper column A with numbers 1 to 21. The latter number is just an example, but it should be att least or larger that the maximum number of Qualities for any given Competency. You can hide the column
4. I use a combination of INDIRECT and INDEX to display the Qualities for the Competencies selected in B2 and D2 on the Comparisons sheet.
5. Copied the formulae in B2 and D2 down to row 23.
If you want, you can copy column B or D to e.g. column F and add a third Competency into you comparison.
7 Replies
Try the solution in the attached workbook, leaving most of what you already made intact. It's perhaps not the most elegant, but it's simple and it works.
1. I created Named Ranges of your competencies in Overall List.
2. I replaced all spaces in list that populates your dropdown boxes on the Comparisons sheet. This is needed to make the options exactly the same as the names created in step 1. It is needed to make the step 4 work.
3. I inserted a helper column A with numbers 1 to 21. The latter number is just an example, but it should be att least or larger that the maximum number of Qualities for any given Competency. You can hide the column
4. I use a combination of INDIRECT and INDEX to display the Qualities for the Competencies selected in B2 and D2 on the Comparisons sheet.
5. Copied the formulae in B2 and D2 down to row 23.
If you want, you can copy column B or D to e.g. column F and add a third Competency into you comparison.
Try this one.
A few notes:
-keep the cells clear below your lists
-table converted back to range
to the data validation list menu:
=OFFSET('Overall List'!$A$2,0,MATCH($A$2,'Overall List'!$1:$1,0)-1,COUNTA(INDIRECT("'Overall List'!"&CHAR(MATCH(A2,'Overall List'!1:1,0)+64)&":"&CHAR(MATCH(A2,'Overall List'!1:1,0)+64)))-1,1)
I used offset because it's capable of arriving at a cell in the sheet and then expanding to capture more values.
A2 is the starting point. Depending on the selection in the first lookup sheet it will move X number of columns to the right. COUNTA is checking how many entries are in each list which is then used to tell offset how "tall" it should be.
- Hello,
Kindly use List Data Validation to create the drop-down.
To create list data validation, click on Data Validation location in the Data Tools group of the Data tab.
Select List from the Allow dropdown
Provide the day source and click OK
Then you can use INDEX/MATCH function
For example
INDEX(B1:E10,MATCH(F10,A1:A10,0),MATCH(G10,B1:E1,0))
Whenever a value is selected from the dropdown, the calculations changes dynamically.
See the attached caption
Hope this helpsThank you so much for your response! However I don't think the index match tool is able to help me list multiple competencies. I have attached the file for you to look at.
Thanks again!
