Using a named list in data validation.

[CRE_finance_guy]

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.

 

[Toadstool]

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.

 

[CRE_finance_guy]

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:

 

[Toadstool]

 

[CRE_finance_guy]

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.

 

[Toadstool]

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.

 

[CRE_finance_guy]

Hmmmm. Maybe. I recall this is this something Excel does allow for. I'll have to do more research on this.