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
9 comments
-
Gaetan Mourmant
commented
As we impatiently wait, a workaround is to use the offset function (that was the method of choice before the tables). I tested it on a series of dependent lists and it worked :-)
-
Edward Paul commented
My case is a bit different from yours, as in my case i was frequently getting the Access data validation error in my Excel application.
When i searched about this specific Excel This Value Doesn’t Match The Data Validation Error, i got this informative post:
http://www.repairmsexcel.com/blog/stuck-ms-excel-data-validation-error-quick-fix-get-going
-
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.