Home » Excel Templates » How To Create Dropdown List From Another Sheet In Excel

How To Create Dropdown List From Another Sheet In Excel

Thursday, October 2nd 2025. | Excel Templates

Creating dynamic dropdown lists in Excel, where the options in one dropdown depend on the selection made in another, is a powerful technique for building user-friendly and efficient spreadsheets. This guide will walk you through the process of creating such dependent dropdowns, pulling the data for these dropdowns from separate sheets within your Excel workbook.

Understanding the Goal

Imagine you have a spreadsheet for managing products. You want one dropdown to list product categories (e.g., Electronics, Clothing, Food), and then a second dropdown should display only the products within the selected category. The product categories and corresponding product lists reside on different sheets within the Excel file.

Preparation: Data Organization and Naming Ranges

The foundation of dependent dropdowns lies in properly organizing your data and using named ranges. Here’s how to prepare your Excel workbook:

Category Sheet: Create a sheet named “Categories” (or something similar). In the first column (column A, for example), list your product categories. Each category should be in a separate cell. Avoid blank rows within the list.

Example:
```
                A1: Electronics         A2: Clothing         A3: Food            
```
Product Data Sheets: Create separate sheets for each product category. Name these sheets after the categories themselves (e.g., “Electronics,” “Clothing,” “Food”). In each of these sheets, list the products belonging to that category in the first column (column A, for example). Again, avoid blank rows within the list.

Example (Electronics sheet):
```
                A1: Laptop         A2: Smartphone         A3: Tablet         A4: Headphones            
```
Example (Clothing sheet):
```
                A1: Shirt         A2: Pants         A3: Dress         A4: Jacket            
```
Naming the Category Range: Select the range of cells containing your categories on the “Categories” sheet (e.g., A1:A3). Go to the “Formulas” tab on the Excel ribbon and click “Define Name” (or press Ctrl+Shift+F3). In the “Name” field, enter a name for this range, such as “CategoryList”. Make sure the “Refers to” field correctly reflects the selected range. Click “OK”. This is crucial; this named range will be used as the source for the first dropdown.
Naming Product Ranges Dynamically: This is the most important and often most complex step. We need to name the product ranges on each category sheet using the *same* name as the sheet itself. This allows us to use the INDIRECT function later. Select the range of cells containing the products for a *single* category (e.g., on the “Electronics” sheet, select A1:A4). Define a name for this range. The *name must be the same* as the sheet name (“Electronics” in this example). Repeat this process for each product category sheet, ensuring the named range matches the sheet name.

Creating the Dropdowns

Select the Cell for the Category Dropdown: Go to the sheet where you want to create your dropdowns. Select the cell where you want the first dropdown (the one for categories) to appear (e.g., cell B1).
Create the Category Dropdown: Go to the “Data” tab on the Excel ribbon. Click “Data Validation”. In the “Settings” tab of the Data Validation dialog box, choose “List” from the “Allow” dropdown. In the “Source” field, type `=CategoryList` (the name you gave to the category range). Make sure the “In-cell dropdown” box is checked. You can optionally go to the “Error Alert” tab and customize the error message that appears if someone enters invalid data. Click “OK”. You now have a dropdown list of your categories in cell B1.
Select the Cell for the Product Dropdown: Select the cell where you want the second dropdown (the one for products) to appear (e.g., cell C1). This dropdown will dynamically change based on the selection in the category dropdown.
Create the Product Dropdown with INDIRECT: Go to the “Data” tab and click “Data Validation”. In the “Settings” tab, choose “List” from the “Allow” dropdown. This is where the magic happens. In the “Source” field, type the following formula: `=INDIRECT(B1)`

Explanation of the INDIRECT Formula:
- INDIRECT(B1): The INDIRECT function takes a text string as an argument and treats that string as a cell reference. In this case, B1 contains the selected category from the first dropdown. Because we named each product range on each sheet with the sheet’s name, `INDIRECT(B1)` will return the range of cells on the sheet whose name matches the category selected in B1. For instance, if “Electronics” is selected in B1, INDIRECT("Electronics") will refer to the named range “Electronics” on the “Electronics” sheet (which contains the list of electronics products).
Again, ensure the “In-cell dropdown” box is checked. Customize the “Error Alert” tab if desired. Click “OK”.

Testing and Troubleshooting

Test Your Dropdowns: Select a category from the first dropdown (cell B1). The second dropdown (cell C1) should now display only the products associated with that category.
Common Issues and Solutions:
- #REF! Error: If you see a #REF! error in the product dropdown, it usually means that the INDIRECT function is failing to find a named range that matches the text in the category dropdown. Double-check the following:
  - Ensure that you’ve correctly named each product range on each category sheet and that the name exactly matches the sheet name (case-sensitive).
  - Verify that the category names in your “Categories” sheet exactly match the sheet names. Any typos or extra spaces will cause the INDIRECT function to fail.
  - Make sure there are no hidden spaces at the beginning or end of the category names in either the “Categories” sheet or the sheet names themselves. You can use the `TRIM` function to remove these spaces.
- Dropdown List is Empty: If the product dropdown is empty, the INDIRECT function is likely resolving to an empty range. Check the same things as for the #REF! error.
- Data Validation Not Working: If the dropdowns don’t appear at all, make sure that data validation is enabled for the cells. Also, check that the “In-cell dropdown” box is checked in the Data Validation settings.
- Categories Not Showing: If the first dropdown (category) isn’t working, double-check that the named range “CategoryList” is correctly defined and refers to the correct range on the “Categories” sheet.

Advanced Techniques

Dynamic Named Ranges with OFFSET: If your product lists are likely to change in size frequently (e.g., you add new products often), you can use the `OFFSET` function within your named range definitions to create dynamic named ranges that automatically adjust to the size of the data. This avoids the need to manually update the named ranges every time you add or remove products.

For example, if your product list starts in cell A1 on the “Electronics” sheet, you could define the named range “Electronics” (for the “Electronics” sheet) using the following formula: `=OFFSET(Electronics!$A$1,0,0,COUNTA(Electronics!$A:$A),1)`. This formula starts at cell A1, moves zero rows and zero columns, and then defines a range that is as tall as the number of non-blank cells in column A (using `COUNTA`) and one column wide.
Handling Errors with IFERROR: You can use the `IFERROR` function to gracefully handle cases where the INDIRECT function fails (e.g., if the user enters a category that doesn’t exist). For example: `=IFERROR(INDIRECT(B1),””)`. This formula will return an empty string if the INDIRECT function returns an error, preventing the #REF! error from appearing in the product dropdown. Alternatively, you could display a more descriptive message like “Invalid Category”. However, using this method means the dropdown will be empty if there is an error instead of showing the #REF error. This may or may not be desirable depending on the specific use case.

Conclusion

Creating dependent dropdown lists in Excel using data from different sheets significantly improves the usability and data integrity of your spreadsheets. By carefully organizing your data, utilizing named ranges, and leveraging the power of the INDIRECT function, you can build dynamic and responsive interfaces that streamline data entry and analysis. Remember to pay close attention to naming conventions and troubleshoot any errors carefully to ensure your dropdowns function correctly.

Post Views: 1