Data Validation Will Not Accept a Named Range

Question

Hi all.

I have a data set and have created a named range ("Names") for a column (M2:M10000) which provides a unique list sorted in alphabetical order.

The named range works fine when using it in a blank cell. however, when I go to use it for a data validation list I get the error: "The source currently evaluates to an error. Do you want to continue?".

Is there a way to use named ranges in a data validation list?

I do not wish to modify the data in any way (including creating a helper list).

Thank you.

Answer 1

Hi Guybrush_Threepwood_ 82, 

Thank you for reaching out to us. I'm glad to help you! 

Based on your description of the "The source currently evaluates to an error. Do you want to continue?" message when using a Named Range in Data Validation, I understand the core issue lies in how Excel's Data Validation interprets your data source. While your Named Range works perfectly elsewhere, Data Validation has quite specific requirements for how it recognizes a list. 

This problem typically occurs when your Named Range is pointing to an Excel Table Name or a Structured Reference from an Excel Table. Data Validation doesn't directly accept these types of references as a source. 

Based on my experience, you need to ensure your Named Range points directly to a specific cell range. But depending on scenario, I hope this may help you. 

**Check your named range:**Go to the Formulas tab > Click Name Manager. 

Find your named range called "Names". 

Look at the Refers to field. If it says something like =Table1[ColumnName], please change it to a direct cell range. For example: ='Sheet1'!$M$2:$M$10000 (Replace "Sheet1" with the actual name of your sheet.) 

**Reapply Data Validation:**Select the cell(s) where you want the dropdown list. 

Go to the Data tab > Click Data Validation. 

Under Allow, choose List > In the Source box, type: =Names 

 

Excel's Data Validation is designed to recognize references to traditional cell ranges or comma-separated lists of items as sources. When your Named Range directly points to a clear cell range (e.g., ='Sheet1'!$M$2:$M$10000), Data Validation can correctly interpret and use it, eliminating the error you're encountering. 

Please let me know once you’ve tried this. I’ll be happy to continue assisting based on your response. Please understand that our initial response may not always resolve the issue right away. But with your help and a bit more detail, I'll try my best to help you. 

Looking forward to your reply! 

Sincerely, 
Jeanie-H - MSFT | Microsoft Community Support Specialist.

Answer 2

Thank you. I have checked this and it is still throwing an error.

This is what I am using/doing:

Answer 3

Hi Guybrush_Threepwood_ 82,

Thank you again for your follow-up and for sharing the screenshots, they were very helpful.

I’ve reviewed your setup, and I can confirm that your formula is now syntactically correct. However, the reason you're still seeing the error message: "The source currently evaluates to an error. Do you want to continue?" is because Excel's Data Validation does not support dynamic array functions like SORT, UNIQUE, or FILTER when used directly in a named range. This is a known limitation of Excel’s Data Validation feature. While Microsoft does not yet have a dedicated support article confirming this limitation, it has been widely discussed and confirmed by the community. You can refer to this Microsoft Answers thread for more context: Excel - Want to use "Data Validation" to create an in-cell dropdown - Microsoft Community

So can you help me to try the steps I suggest as below, I hope it works for you.

Create a helper column (column N) and enter your formula there: =SORT(UNIQUE(FILTER(Data!$M$2:$M$10000, Data!$M$2:$M$10000<>"")))

Define a new named range (PartnerList) that refers to the static range in column N (=Sheet1!$N$2:$N$100).

Use =PartnerList in your Data Validation source.

This approach helps compatibility with Excel’s Data Validation engine, which only accepts static ranges or comma-separated lists.

Could you please give this workaround a try and let me know if it works for your case? If you get stuck at any step or run into a different issue, feel free to reply. I’ll be happy to assist further!

Sincerely, 
Jeanie-H - MSFT | Microsoft Community Support Specialist.

Answer 4

To make this long story short. You need to enter your formula somewhere on your worksheet. Let's say in cell N2. Then, point the source for the data validation list to $N$2#, as shown in the picture below. It's taken on a Mac but is similar to what you get on a PC.

The # at the end creates a dynamic reference to the array that begins in N2 which in turn contains the SORT-UNIQUE-FILTER formula.

Answer 5

Hi Guybrush_Threepwood_ 82,

Just checking in to see if you had a chance to read my earlier message. Please let me know if you need any clarification or if there's anything else I can help with.

Thanks again, and I appreciate your time!

Sincerely, 
Jeanie-H - MSFT | Microsoft Community Support Specialist.