Using a named range in Data Validation list

jimbojr

New Member
Joined
Dec 30, 2002
Messages
43
Friends,

I'm hoping there's a simple (non VBA) solution to this question. I want to use a named range of value as the lookup list for Data Validation. The range is on the same sheet as the cell I want to validate.

It appears, however, that any lists reference the lookup values by absolute cell references.

Can I use the values in my named range as my validation list?

Jimbo
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
yes, i actually prefer a named range for data validation -> list.

"=ListName"

should work.

hope this helps.
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
on the validation list on the allow field choose "list"
then on the data field enter
=myrangename
then you have your validation list.

is this what you mean?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Data -> Validation -> Allow -> List

in the source box type:

=ListName

just remember when you name your list, that if you want there to be a blank entry, include that in your list name. (y)
 

jimbojr

New Member
Joined
Dec 30, 2002
Messages
43
Re: Using a named range in Data Validation list - SOLVED

Thanks to all who responded.

You solution is what I tried the first time (seemed intuitive enough). Yet, instead of getting the list of values in the range in my validation drop-down, I got the range name itself.

I must have screwed up my formula, as simple as it was, somehow.

It works just fine!

Thanks again!

Jimbo
 

RobertWaters

New Member
Joined
Mar 4, 2016
Messages
1
Re: Using a named range in Data Validation list - SOLVED

Thanks to all who responded.

You solution is what I tried the first time (seemed intuitive enough). Yet, instead of getting the list of values in the range in my validation drop-down, I got the range name itself.

I must have screwed up my formula, as simple as it was, somehow.

It works just fine!

Thanks again!

Jimbo

It's all to do with the quotes. If you use

"=NamedRange"​

then the dropdown will display

NamedRange​

but if you use

=NamedRange​

then the dropdown will display the values in the named range
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,564
Messages
5,765,129
Members
425,262
Latest member
sabry

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