Now, we're ready to use the INDIRECT function to turn the data in cell E1 into a reference. At this point, our aim is to find ...

How is indirect function used in data validation? On the spreadsheet, select cell C3. On the Ribbon, click the Data tab, then click Data Validation. From the Allow drop-down list, choose List.

Use the INDIRECT function: In the data validation settings for the dependent drop-down list, use the formula =INDIRECT(A1), where A1 is the cell reference for the primary drop-down list.

Next, create a validation rule as you did before, but enter the following function, which references the named range instead of using INDIRECT(): =COUNTIF(MNumber,B3)<=1 ...

By combining data validation with the INDIRECT function, you can create dynamic dependent lists that automatically update based on selected criteria. For instance, ...

Drop-down lists in Excel let you create a list of valid choices that you can select for a given field. We'll show you how to use tables, named ranges, formulas, data validation, and table styles.