Dynamically pick a List based on a cell content using INDIRECT Function

The INDIRECT function in Excel can be used to dynamically reference a cell or range of cells based on the text string entered into another cell. To use the INDIRECT function to pick a list from Excel data validation, you can follow these steps:

  1. Create a named range for each list you want to use in your data validation. To do this, select the cells containing the list, then click on the “Formulas” tab, select “Define Name” and give the named range a descriptive name.
  2. In the cell where you want to use data validation, create a dropdown list by selecting the cell and clicking on “Data Validation” in the “Data” tab.
  3. In the “Data Validation” dialog box, select “List” in the “Allow” dropdown, and in the “Source” field, enter the formula using the INDIRECT function to reference the named range you want to use. For example, if you have a named range called “Fruit” that contains a list of fruit names in cells A1:A5, the formula would be: =INDIRECT(“Fruit”).
  4. Click “OK” to close the dialog box, and you should now have a dropdown list that uses the named range specified by the INDIRECT function.

Note that the named range used in the INDIRECT function must be enclosed in quotation marks and entered as a text string, and the named range must exist in the same workbook as the cell containing the data validation.

Watch Video

Download Excel Workbook Here

[download id=”1361″]

Leave Comment

Your email address will not be published. Required fields are marked *

OCL Learning | Training, Consulting, Certification