Forum Discussion
How to Populate a List from a Drop Down List?
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.
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 helps
Thank 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!
