Excel 用另一張工作表做資料驗證列表

Tested in: The COUNTIF membership checks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Data Validation or Name Manager in Excel this session.

Creating a list on the same sheet is in How to Add a Drop-Down List in Excel. If the arrow is missing or the list is blank, see drop-down list not working. This page is only about pointing the source at another sheet.

Point the Source box at the other sheet

On the Lists sheet, keep a single header in row 1 and the items in A2 down. On the sheet where people type, select the input cells and open Data > Data Validation:

1. Set Allow to List.
2. In Source, enter:

=Lists!A2:A20

3. Leave In-cell dropdown checked, and click OK.

In Google Sheets the same reference works in the Criteria (dropdown from a range) box, including a range on another tab. If the sheet name has a space or punctuation, wrap it in single quotes: ='Item lists'!A2:A20.

Do not include the header. =Lists!A1:A20 puts “Region” (or a blank, if A1 is empty) at the top of the drop-down.

Use a named range when the dialog refuses a sheet reference

Some older Excel builds will not accept =Lists!A2:A20 in the Source box. A named range works around that and is easier to reuse on several input sheets:

1. Select Lists!A2:A20 (items only).
2. Open Formulas > Define Name (or Name Manager > New).
3. Name it RegionList. The refers-to box should read =Lists!$A$2:$A$20.
4. On the input sheet, set the validation source to =RegionList — no quotes, and no sheet name in front of the name.

If you later insert rows inside that range, the name expands with them. If you type new items below A20, either widen the name or convert the list to a table and point the name at the table column (=Lists[[#All],[Region]] is usually the wrong form here because it can pull the header; a structured reference that starts at the data column, or a range one row below the header, is safer).

Check that the list actually contains the value

After the drop-down is in place, confirm membership from the input sheet:

=COUNTIF(Lists!A2:A20,B2)

or, with the named range:

=COUNTIF(RegionList,B2)

A result of 1 means B2 is in the list; 0 means it is not. In the test workbook, =COUNTIF({"East";"West";"North"},"East") returned 1 and the same check for "South" returned 0. If you see 0 for a value that “looks” like a list item, the usual causes are a trailing space, a different spelling, or pointing COUNTIF at the wrong column — not a failure of the cross-sheet reference itself.

What this is not

A drop-down whose items change when someone picks a category in another cell is a dependent drop-down. That setup still often stores each category list on another sheet, but the Source box uses INDIRECT (or a FILTER) to pick which list. Get the simple =Lists!A2:A20 form working first, then add the dependent layer.

A list in another workbook is a different problem again. Excel can be picky about validation sources that point at a closed file; keep the list in the same workbook when you can.

Once Source accepts the other sheet and COUNTIF returns 1 for a known item, the cross-sheet list is doing its job. Any remaining “will not appear” symptoms belong on the troubleshooting page.