RECORDS / TERMS / THE NEXT APPOINTMENT

Excel Data Validation Dropdown List from Another Sheet

How to build an Excel data validation dropdown list from another sheet using a cross-sheet reference, a named range, or the INDIRECT function.

3 min read1 views
— · 0 votes
Contents
  1. Point the source at the other sheet
  2. Use a named range for a stable source
  3. Reach lists with the INDIRECT function
  4. Questions about cross-sheet dropdown lists
  5. How do I make a dropdown list from another sheet in Excel?
  6. Can I use a named range as the data validation list source?
  7. Why does my dropdown break when the source sheet changes?
  8. What does the INDIRECT function do in a dropdown?

An Excel data validation dropdown list from another sheet is built by pointing the list source at a range on a separate worksheet instead of typing the options into the dialog. The Data Validation dialog accepts a formula or a defined name in its Source box, and that reference keeps the entries in one place. The steps below cover the direct cross-sheet reference, a named range, and the INDIRECT function for dependent lists.

Point the source at the other sheet

A cross-sheet reference in the Source box lets the dropdown read entries that sit on a list on a separate worksheet. Open the Data Validation dialog, choose List as the allowed type, and enter the sheet name with an exclamation mark before the cell range. The reference works while both workbooks stay open, which matters when the list lives in a different file. Test the dropdown after you confirm the settings.

Use a named range for a stable source

A named range gives the data validation list source a fixed name, so the dropdown keeps working when rows move on the source sheet. Define the name over the cells that hold the options, then type that name into the Source box. Names are easier to audit than raw addresses, and a reader can see where the entries live. Update the range the name covers when the option list grows.

Reach lists with the INDIRECT function

The INDIRECT function turns text into a live reference, which lets a dropdown follow the value chosen in another cell. Each option in the first list carries the name of a range that holds its own entries. The second dropdown uses INDIRECT on the chosen value to pull the matching list from the other sheet. Names used by INDIRECT must exist and must not contain spaces, so check the spelling of each one.

Questions about cross-sheet dropdown lists

How do I make a dropdown list from another sheet in Excel?

You make a dropdown list from another sheet by using the Data Validation dialog and setting the allowed type to List. In the Source box, enter a cross-sheet reference such as the sheet name, an exclamation mark and the cell range. The dropdown then shows the entries stored on that separate worksheet. Keep both sheets in the same workbook where possible to avoid broken links.

Can I use a named range as the data validation list source?

Yes, a named range can serve as the data validation list source and keeps the reference readable. Define a name over the cells that hold the options, then type that name into the Source box of the dialog. The dropdown reads the entries through the name, not through a typed address. Redefine the name when you add rows so the new options appear.

Why does my dropdown break when the source sheet changes?

A dropdown breaks when the source sheet changes because the reference points at cells that no longer hold the entries. Deleting rows, renaming the sheet, or moving the list leaves the old address pointing at empty cells. Check the Source box and repoint it at the range that holds the options now. A named range reduces this risk because the name follows the cells you redefine.

What does the INDIRECT function do in a dropdown?

The INDIRECT function converts text into a reference, so a dependent dropdown can look up the list named by another cell. The first dropdown sets a value, and INDIRECT on that value pulls the matching entries from the other sheet. Every name it relies on must exist and must avoid spaces. Dependent lists built this way need consistent naming between the two sheets.

Latest articles