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

7 comments
-
Mourad commented
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
-
Tor Jensen commented
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" -
Mourad commented
Great tip
-
Kevin Osborn commented
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.
-
Anonymous commented
Brilliant Idea. TI would use this day in day out
-
Mourad BENKADOUR commented
yes it will be a great improvement, you have also add functions suggestions in data validation
-
Brandon commented
Yes that would definitely help make things easier for all conditioning formatting too I bet.