Using a named list in data validation.

CRE_finance_guy

New Member
Joined
Dec 3, 2021
Messages
30
Office Version
  1. 2021
Platform
  1. Windows
I'm looking to create a cell with data validation, which utilizes a list that's stored in a name, as opposed to a list that's located on a sheet somewhere. First I started with the named list, which works perfectly. I've called it data_123, and its reference is ={"1";"2";"3"}. But for some reason I haven't been able to get the data validation function to recognize this. When I open the data validation dialogue box, I select "list", and for the source I type "=data_123", but I'm not having any luck. I recall this is possible to do, but I seem to have forgotten a step. Can someone please fill me in on what I'm doing wrong here? Any thoughts would be much appreciated.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi CRE_Finance_guy,

Let's check it sees the name.

Select Data Validation, List then click in Source.
Now select Formulas, Use in Formula. Does that offer Data_123? If so select it and the LoV should work.
 
Upvote 0
Hi Toadstool. Thanks so much for the help. So I can get to Data Validation, then List, then Source, but you said "Now select Formulas", and I'm not sure what you're referring to there. My only option is to input something into the source field. See clipping below:
1638567766526.png
 
Upvote 0
Ah! Pardon me. Thanks for clarifying. So I tried this, and it's the same result. An error message appears when I use this name in the source field. The name is fully-functional elsewhere, but I'm not sure how to get data validation to recognize it.
1638570534064.png
 
Upvote 0
Ah! I've just looked back at the original question.
Excel will let you define a name with a Refers to of ={"1";"2";"3"} but it won't let you use that in a Data Validation list.
 
Upvote 0
Hmmmm. Maybe. I recall this is this something Excel does allow for. I'll have to do more research on this.
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top