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.)
Ute Simon
shared this idea
4 comments
-
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.