Dependent data validation?

Quoted from SituFangs on Tuesday, 22 June 2021

How can I create data validation in one cell that is dependent on what is entered in a different call?Example: In A1 I have a data validation drawing from a list of school subjects. The user can select one of the subjects from the dropdown menu. The dropdown menu in B1 will vary depending on what was selected in A1. If A1 has "Mathematics," B1 will provide options of various math classes. If A1 has "Public Speaking", B1 will provide options of various public speaking classes. <!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}-->10.0.0.1

To achieve this in Excel, you can use named ranges and the INDIRECT function for data validation.

Here are the steps:

1. Create named ranges for the different classes corresponding to each subject. For example, name the range for Mathematics classes as "Math_Classes", and name the range for Public Speaking classes as "PublicSpeaking_Classes".

2. Set up data validation for cell A1 to create a dropdown list of school subjects.

3. Set up data validation for cell B1, but instead of directly referring to a range, you will use the INDIRECT function to dynamically reference the range based on the selection in cell A1.

Here's how you can set up the data validation for cell B1:

1. Select cell B1.
2. Go to the "Data" tab in the Excel ribbon.
3. Click on "Data Validation" in the "Data Tools" group.
4. In the Data Validation dialog box, select "List" from the "Allow" dropdown menu.
5. In the "Source" field, enter the following formula:
```
=INDIRECT(A1 & "_Classes")
```
This formula dynamically references the named range based on the value in cell A1. For example, if "Mathematics" is selected in cell A1, the formula will refer to the named range "Math_Classes", and if "Public Speaking" is selected, it will refer to "PublicSpeaking_Classes".
6. Click "OK" to apply the data validation.

Now, when you select a subject in cell A1, the dropdown list in cell B1 will automatically update to show the corresponding classes for that subject.