We return to the input sheet to set up the dependent dropdown list for the Apps.
To create the dropdown for Division, perform the following steps:
- Select cell B3.
- Select Data -> Data Validation.
- In the Data Validation dialog box, select “List from a range” in the Criteria section and define the range as Master!F4:Z4. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option. A few extra columns were selected to accommodate additions to the table.
The result is a list of Apps for the selected Division.
If we copy the B3 cell and paste it across the remaining Column B cells, we don’t get what we were expecting from the dependent dropdowns.
Every dependent dropdown has the list for whatever Division is selected in cell A3. This is because all dependent dropdown lists are looking at range F4:Z4 for their list of Apps.
This goes back to the implied absolute behavior mentioned earlier in the post. It worked to our advantage then, but now it presents us with a challenge to overcome.
This is a “good news/bad news” situation.
The good news is that the problem can be solved. The bad news is that it will require manual intervention to adjust to the Data Validation settings of each dependent dropdown list.
After having copied the Data Validation settings from cell B3 to cells B4:B12, select cell B4 and open the Data Validation dialog box.
In the Criteria section, change the row references to point to the next row. Instead of F4:Z4, the updated reference should read F5:Z5.
Continue to update the remaining rows, changing the Criteria option to examine the next row in the data preparation area (ex: F6:Z6, F7:Z7, F8:Z8 … F13:Z13).
Thoughts on this Process
If you only require a few rows (i.e., 10 or 20) of dependent dropdown lists, this wouldn’t take too long to construct. If you require hundreds or thousands of these, you may need to investigate writing an App Script to automate the creation process.
The benefit to having an App Script is that once it’s written, you can reuse it as many times as you need in new or existing projects.