data validation named range returning array

Question

What are the detailed limitations of what is allowed in the source of data validation lists?

I have some named ranges that return dynamic arrays (Index functions) and they are not accepted be the data validation dialog (source currently evaluates to an error).

Just as an experiment, I tried creating a named range called test which "refers to" ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}

I got the same error with this.

What are the limitations please?

Answer 1

Data validation with the list setting needs a list as the source or a contiguous range. A dynamic range name that produces such a list will work. Data validation does not work with an array. I don't know if this is documented anywhere. Many things about Excel are not documented (alas!), especially things that don't work.

You could use the CSE formula in a worksheet and then let the range name refer to the resulting range. But it looks as if the array formula is relative to the current cell, so that may not be an option.

I think the answer here is "it can't be done" at least not with a formula that returns an array.

Don't shoot the messenger.

Answer 2

Hi, that's fine but I am constructing a virtual reference with a named range that returns an array.

Your example returns a reference not an array.

The simple example I gave was setting the source to a constant array did you try it?

The actual application I'm attempting is as follows:

=INDEX(Factories[Country],SMALL(IF((Factories[Region]=!RC[-1])*(MATCH(Factories[Country],Factories[Country],0)=Factories[indx]),Factories[indx]),ROW(INDIRECT("1:"&CountriesInRegionCount))))

Factories is a table of factories with columns for Region, Country and Indx (the row of the table)

The Region is in the cell to the left and the CountriesInRegionCount is the number of countries in each region.

The formula constructs a column of height CountriesInRegionCount

It uses the small function along with the match(A,A,0) structure to create a contiguous list of Indx's (locations in the table) where the the Region column matched the cell to the left.

So if the countries in the region are in rows 1,5,and 7, then the formula will construct the rows array in the Index function as {1;5;7;r1;r2;r3...} where r1 r2 etc are the Index value of table records that do not match the criteria.  The Indirect function then constructs a vertical index vector of length CountriesInRegionCount, to feed into the second parameter of the small function, which then serves as the row numbers for the index function

The end result is a vertical vector containing the countries in the region.

This works fine when I CSE it into a vertical range in the spreadsheet but fails in the validation with the error I mentioned in my previous post.

Like I said, it returns an array, NOT a reference and I just want to know if the data validation can accept this or not and I would like some comprehensive documentation on what its limitations are.

Cheers

Answer 3

Fair enough...

I will post a bit later; thanks for the help!

Meanwhile, did you try the simple example with the array constant?

Just try making a named range with refers to ={1;2;3;4;5;6;7;8;9} for example and try to enter that named range in the data validation list...

If you can replicate this then I think this is the route cause, because my array formula works fine as I mentioned...

Answer 4

Can you post a sample file where the formula can be applied? It's a bit hard trying to re-create the scenario from the formula and description.

Answer 5

Hello,

dynamic range names are a valid source for data validation in Excel 2013. Please see the screenshot below. The data validation has been set to "List" which points to the range name "ListData". The formula for that range name is

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$1,Sheet1!$A:$A,1))

When the number in E1 is changed, the data validation drop down will show a different list.

If this does not work for you, chances are that there is an error in the range name formula.

To define a list right inside the data validation dialog, you do not need to enter the curly braces. Just enter the list items, separated by your regional list separator, which seems to be a semicolon. In my case it is a comma and the dialog looks like this:

If you need help troubleshooting your range name formula, please post a sample file with some dummy data and the range name formula.