up vote 1 down vote favorite
share [g+] share [fb]

I am using data validation to create a drop down list on a worksheet. What I'd like to be able to do is set up a list of values as a defined name (Insert > Name > Define...) and then enter that defined name as the source of my data validation list. I'd like to do this because...

  1. If I simply list my drop down values on a worksheet I run the risk that somebody deletes the sheet / column / rows. I can mitigate against this but I think it is neater if the list is encapsulated as a defined name.
  2. I have multiple drop downs, some of which always contain the same fields e.g. Yes / No. It would be easier to type in "=YesNo" as my source each time rather than ="$A$1:$A$2" for example

I know it is possible in the source field of data validation to simply type a delimited list...

source: Oranges, Apples, Pears

...but what I want is to able to to type...

source: =Fruits

...where Fruits is a defined name that refers to Oranges, Apples, Pears.

Can this be done? So far I cannot make it work using simple excel features.

I know I could do it with VBA (e.g. when sheet is activated -> populate dropdowns) but this seems like overkill...

As always, any input welcome...

link|improve this question
feedback

migrated from stackoverflow.com May 19 '11 at 10:16

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote

Works OK for me:

Choose Lists in data validation and type =Fruits in the Source box

link|improve this answer
I get an error "List must be a delimited list, or a reference to a single row or column". This is on Excel 2003.How are you entering the Fruits list in Name > Define? I have used ="Oranges, Apples, Pears" – Remnant Mar 17 '11 at 11:29
@Remnant, I'm pretty sure you can't do this with a literal array. The syntax would be ={"Oranges", "Apples", "Pears"}, with your named formula as an array, but that doesn't work. You can either put the list directly in the validation dialog for the cell (i.e. 'Oranges, Apples, Pears' with no '=' or quotes), or have it on a sheet somewhere. See this answer for more: stackoverflow.com/questions/4783019/… – jtolle Mar 17 '11 at 14:48
@Charles, what am I missing? I've never been able to figure out how to use a literal array for validation, either with a defined name or returned by a UDF. – jtolle Mar 17 '11 at 14:50
@jtolle - thanks for the input. I agree with your conclusion - having done quite a bit of googling I don't think I can do what I have asked. I agree with the two options you list as alternatives... – Remnant Mar 17 '11 at 17:32
I had not spotted that Remnant wanted a literal array. I don't think it can be done without using a literal list or using a Named Range. – Charles Williams Mar 18 '11 at 8:22
feedback
up vote 0 down vote

I don't have Excel 2003 anymore but I am pretty sure what you are asking works. Please:

  • Check you used "=" explicitly in the validation definition (i.e. write "=Fruits")
  • Check whether the named range reference works once the referenced range is on the same page of the cell where you use the validation (there was a contraint on this for some time)
link|improve this answer
@Paolo - I agree it works when referencing a range on a worksheet. However this is not what I want (see original post) - I want a defined name that is decoupled from a range in a worksheet. I am fairly sure this cannot be done (see comments under Charles William's answer) – Remnant Mar 20 '11 at 18:31
feedback

Your Answer

 
or
required, but never shown
discard