2

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...

Improve this question

2 Answers 2

Reset to default
0

Works OK for me:

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

Improve this answer
5
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 8:22
0

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)
Improve this answer
1
  • @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, 2011 at 18:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .