Use new dynamic arrays in data validation

The use helper columns in tables now often is unnecessary if using the the new dynamic arrays. But if I want to use the output of one of the new functions as the source for a data validation list, I need to put the function in a cell and reference to that cell as the source for the data validation list. I would like to be able to put the dynamic array function directly into the source field of the data validation dialog.
Example: I have a table with a column of countries. I want to add a cell with a dropdown to chose one of those countries. I have to create a list of country names on the sheet using UNIQUE and SORT and then reference to the cell with this formula as the source for my data validation dropdown. I would prefer inputting the formula directly into the data validation without the helper list on the sheet.
(A similar suggestion has been posted for Excel on Mac, this adds and elaborates the suggestion for Excel for Windows.)

63 votes

Ute Simon shared this idea · September 28, 2020 · Flag idea as inappropriate… · Admin →

7 comments

An error occurred while saving the comment

Mourad commented · April 02, 2021 08:42 · Flag as inappropriate

What is Excel team waiting for to apply it, it's very frustrating each time to make a table preparation intead of put the Dynamic array function into Data Validation, and add Intellisense

Submitting...
Tor Jensen commented · February 28, 2021 03:34 · Flag as inappropriate

You can do this you just need to "go through" the name manager, similar to when you use tables.

Define a name as your dynamic array formula
In your data validation, set list definition "=defined_name"

Submitting...
Mourad commented · January 26, 2021 04:32 · Flag as inappropriate

Great tip

Submitting...
Kevin Osborn commented · January 05, 2021 05:34 · Flag as inappropriate

Data validation not only needs to allow the formulas that create dynamic arrays but also table/structured reference and formula editing/validation as if the formula was being entered in a cell and not just tell you when you click OK that the formula is not valid.

Submitting...
Anonymous commented · December 17, 2020 01:18 · Flag as inappropriate

Brilliant Idea. TI would use this day in day out

Submitting...
Mourad BENKADOUR commented · October 05, 2020 08:14 · Flag as inappropriate

yes it will be a great improvement, you have also add functions suggestions in data validation

Submitting...
Brandon commented · September 30, 2020 10:33 · Flag as inappropriate

Yes that would definitely help make things easier for all conditioning formatting too I bet.

Submitting...