1
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...
- 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.
- 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...
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" – None – 2011-03-17T11:29:58.063
@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: http://stackoverflow.com/questions/4783019/can-i-use-vba-function-to-return-a-dynamic-list-of-acceptable-values-into-excel/4797514#4797514
– jtolle – 2011-03-17T14:48:51.730@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 – 2011-03-17T14:50:37.227
@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... – None – 2011-03-17T17:32:32.033
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. – None – 2011-03-18T08:22:29.367