In the same way, go to Data Validation under the Data tab and select List.
However, unlike the first dropdown list, a direct cell reference can’t be used for the second list since the source will be different depending on the selected option in the first list.
- Productivity Div: $A$5:$A$19
- Game Div: $B$5:$B$19
- Utility Div: $C$5:$C$19
Instead, a combination of Excel’s INDEX() and MATCH() function will be used.
However, writing the formula directly on Source box of the Data Validation window will not give you the Excel help to assist you with the syntax.
One option is to write the formula in a random cell in the spreadsheet and then copy and paste that as the Source in the Data Validation window.
The syntax of INDEX() is:
=INDEX(array, row_num, [column_num])
- array: The area where the answer could be. In this case it is $A$5:$C$19.
- row_num: This dictates how many rows to move down the array in order to find the answer. Since we want to include all the rows, leave this blank.
- column_num: Indicates what column number the answer list is found in. Since we want this to be dynamic according to the selection in the first dropdown list, the MATCH() function will be used. This will return a number to dictate how many columns to move to the right to find the answer.
The syntax of MATCH() is:
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: The value you want to look for. In this case, it is $G$4.
- lookup_array: This is where to look for the lookup_value. $A$4:$C$4.
- match_type: There are three options, “1- Less than”, “0 – Exact match”, and “-1 – Greater than”. Use 0.
The final formula is:
Since this will be pasted to Data Validation, make sure to fix the cell references by adding $ or pressing the F4 key. (Here is a link to other INDEX MATCH guides.)
When you hit ENTER, the value will not show the full list result of the formula, but instead only display the contents of one of the cells.
To check if it returns the correct list, go to the formula bar and hit the F9 key.
It will then show the contents.
Press ESC or CTRL Z to leave this view and revert back to the normal formula.
Once you get the results needed, highlight the entire formula, right click and select Copy (or press CTRL C).
Go to the cell where you want the second dropdown list, cell G5.
Go to Data Validation, select List, and in Source, paste into the Data Validation list.
Click OK and you will see that the second dropdown list changes according to the chosen option for the first dropdown.