Not able to paste formula returning array in "list" of data validation in Excel

Question

I am trying to build a dynamic dependent list for data entry. All this works very well.

When I build a formula to return an array in the cell of the workbook, it works fine.

When I try to copy that formula in the list option in data validation, there is an error. Excel doesn't like it.

Interestingly, multiple youtube videos show the same formula being copied in list and it works. All those videos are 3 years or older. Is there a change in Office 365 now? what is the workaround?

Formula I am trying copy is =INDEX(Table7,,MATCH(R13,WA,0))

Answer 1

Data validation accept range instead of array. Though your formula can give a correct result, but it cannot be put into Data validation.

You may put your formula in another cell like Z2, then use "=Z2#" in data validation.

=Z2#

Answer 2

Now I got new error " the source currently evaluates to an error."

=SORT((FILTER(UNIQUE(INDEX(list_Countries,,MATCH(R13,WA,0))),UNIQUE(INDEX(list_Countries,,MATCH(R13,WA,0)))<>0)))

This works well in cell but as soon as I paste it in list of data validation error pops up.

Answer 3

You cannot use a table name in the source of data validation of type List.

As a workaround, select the data body of the table (i.e. the table without its header row).

Click in the Name box on the left hand side of the formula bar. It should display Table7.

Assign a new name, for example Table7Range.

You can then use

=INDEX(Table7Range,,MATCH(R13,WA,0))

in the Source box of your data validation.

The named range Table7Range will grow/shrink dynamically as you add or remove table rows.

Answer 4

Use cell reference instead of table name.

Suppose you table range is A3:E6

Change the table7 to A3:E6. Then it will work.

=INDEX(A3:E6,,MATCH(R13,WA,0))