Using a named list in data validation.

CRE_finance_guy

New Member
Joined
Dec 3, 2021
Messages
28
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 3, 2021
Messages
28
Office Version
  1. 2021
Platform
  1. Windows
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
 

CRE_finance_guy

New Member
Joined
Dec 3, 2021
Messages
28
Office Version
  1. 2021
Platform
  1. Windows

ADVERTISEMENT

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
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 3, 2021
Messages
28
Office Version
  1. 2021
Platform
  1. Windows
Hmmmm. Maybe. I recall this is this something Excel does allow for. I'll have to do more research on this.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

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
Top