Creating a Dropdown List in Excel from a Horizontal Range

Requirements

To create a dropdown list that pulls information from a horizontal range based on a selected name in a different row, follow these structured steps:

1. Data Preparation: Ensure you have a horizontal row of data and a vertical column for names.
2. Name Selection: Set up a cell where you can input or select a name.
3. Dynamic Dropdown: Create the dropdown list that dynamically changes based on the selected name.

Steps to Implement

1. Prepare Your Data

Assuming you have a dataset structured as follows:

• Names in Row 1 (e.g., A1: "Name1", B1: "Name2", C1: "Name3")
• Corresponding values in Row 2 (e.g., A2: "Value1", B2: "Value2", C2: "Value3")

2. Define the Name Selection Cell

Choose a cell to select the name, for example, D1.

3. Create the Dropdown List

To create a dynamic dropdown list in cell E1 based on the name selected in D1:

Step A: Define the Data Range

1. Name the Range: Select the horizontal data range (e.g., A1:C1) and define a named range. Go to the Formulas tab → Define Name, and set the name as NameList.

Step B: Create a Dynamic Dropdown

1. Select Cell for Dropdown: Click on E1 (the cell where the dropdown will appear).
2. Create Data Validation:

   • Navigate to the Data tab.

   • Click on "Data Validation."

   • In the "Allow" box, select "List."

   • In the "Source" box, enter the formula:

     =INDIRECT(VLOOKUP(D1, A1:C2, 2, FALSE))

Explanation of the Formula

• VLOOKUP(D1, A1:C2, 2, FALSE): This function searches for the name entered in D1 within the range A1:C2. It looks for the name in the first row and returns the corresponding value from the second row.
• INDIRECT(): This function converts the text string returned by VLOOKUP into a reference, allowing the dropdown list to populate with the correct values based on the selected name.

4. Example

• If you select "Name1" in cell D1, the dropdown in E1 will show "Value1".
• If you select "Name2" in cell D1, the dropdown in E1 will show "Value2" and so forth.

Conclusion

This process effectively creates a dropdown list in Excel that changes based on the selected name, utilizing VLOOKUP for matching and INDIRECT for generating a dynamic dropdown range.

For further learning on data validation and dynamic ranges, consider exploring resources available on the Enterprise DNA Platform.