设置data validation时选用指定行而非整范围报错求助

Hey Anna, I’ve dealt with this exact Excel data validation headache before—totally get how annoying it is when you need to pick specific non-contiguous rows and hit that error message. Let’s break down the problem and fix it with a couple of solid solutions:

Why the Error Happens #

Excel’s default "Sequence" data validation only accepts two types of sources:

• A single continuous row or column (like your original $A$4:$A$76 range)
• A comma-delimited list of values (typed directly into the source box)

It doesn’t let you select non-contiguous cells directly, which is why you’re seeing that frustrating error prompt.

Solution 1: Manual Comma-Delimited List (Quick & Simple) #

If your values don’t change often, this is the fastest way to get it working:

1. Open the Data Validation dialog (Data tab → Data Validation)
2. Under "Allow", select "Sequence"
3. In the "Source" field, type references to your specific cells separated by commas. For example:

   =$A$11,$A$22,$A$31,$A$55,$A$59,$A$60,$A$70,$A$71,$A$72,$A$75
   

   Pro tip: If any value contains a comma (like "Los Angeles, CA"), wrap that value in double quotes to avoid confusion: "Los Angeles, CA",New York

Solution 2: Named Range with Formula (Dynamic & Scalable) #

If your values might update later, this method keeps your validation list linked to the original cells so you don’t have to retype everything:

1. Press Ctrl + F3 to open the Name Manager
2. Click "New" and name your range something descriptive (e.g., ValidationTargetCells)
3. In the "Refers to" box, paste this formula (replace the numbers with your row indices):

   =INDEX($A:$A,{11,22,31,55,59,60,70,71,72,75})
   

   This formula pulls values from your specific rows into a virtual continuous range Excel can recognize.
4. Save the named range, then go back to Data Validation:
   • Select "Sequence" under "Allow"
   • In "Source", type =ValidationTargetCells and hit OK

Bonus: Excel 365/2021 Shortcut #

If you’re on a newer Excel version, use TEXTJOIN to auto-generate the comma list for you:

1. In a blank cell (say, B1), enter:

   =TEXTJOIN(",",TRUE,$A$11,$A$22,$A$31,$A$55,$A$59,$A$60,$A$70,$A$71,$A$72,$A$75)
   

   The TRUE parameter skips any blank cells in your selection automatically.
2. Then set your Data Validation source to =$B$1 (just reference the cell with the TEXTJOIN result)

All these methods should get your data validation working with those specific rows without hitting that error again!

内容的提问来源于stack exchange，提问作者Anna