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

Good day, Guybrush_Threepwood_ 82!

I hope you are doing well.  

My apologies for reaching out to you again. Regarding your thread (Data Validation Will Not Accept a Named Range - Microsoft Community).

We are glad that helps. Meanwhile, hope you kindly mark it as an answer and vote it up by your original account which raised this question (as seen in the screenshot). Once marked, it will automatically pin to top. As other users will also search information in this community, your valuable marked answer will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.  

Greatly Appreciate again for your patience and understanding.  

I wish you keep safe and wish you all the best in year 2025 as well! 😊

Warm regards,
Katherine-V - MSFT | Microsoft Community Support Specialist.

Answer 2

Hi Katherine.

Thank you for your responses to date. Much appreciated.

Whilst I cannot advise the responses resolved my problem, I found them helpful in the sense it made me aware a solution within Excel is not possible and I could stop searching, however I cannot see an option of "Was this reply helpful" as per your post - I only have the option to select that it resolved my problem.

Answer 3

I found them helpful in the sense it made me aware a solution within Excel is not possible and I could stop searching,

There is a solution, Riny already showed it in this thread, you either missed it or ignored it.

Sample file:
https://www.dropbox.com/scl/fi/qhos3unbgp4kfryz9a3cb/3cf6e2cc-1678-47ea-b765-9ab7f3aacc15.xlsx?rlkey=nys955fgwbuw23ivlw5p6udnu&dl=1

Andreas.

Answer 4

I do not wish to create a helper list or modify the data.

This was in the OP, you either missed it or ignored it.

Answer 5

In this case, all you can do is to send a frown to Microsoft and wait till they changed the behavior.

How do I give feedback on Microsoft Office? - Office Support

Unfortunately, there's nothing more we can do from here. I'm sorry.

Andreas.