Filter Function In Data Validation Not Working

Question

Excel 1909 Build 12001.20000 August 2019

Using Filter() function. (=FILTER(Table4[Date],Table4[Value]<>0,"")

Works great.

Cannot use this same function and syntax as a source for a Data Validation (List).

Currently, I can create a filtered list on my worksheet, then use the Offset() function (in the data validation) but his requires a syntax of Offset($B$2,,,Count($B$2:$B:xxx)) where xxx is an arbitrary max number of rows.

I think the filter() function should be accessible in the Data Validation, in order to allow for a filtered list of values in a drop down list.

Has anyone gotten the filter() function to be usable in data validation?

Answer 1

It sorta works.

1. Data validation, like Conditional formatting, still doesn't work well with (or at all) table references. So replace those with absolute references. So =FILTER($F$10:$F$15,$F$10$:F$15<>3) for example.
2. When you enter it Excel will report with "The Source currently evaluates to an error. Do you want to continue?" Answer Yes.

The dropdown will show up, but not work. However, in my test, it would not let me enter a 3, which is what I was filtering out of a simple list of 1-6. Entering anything but 1,2,4,5,6 would return a data validation error to the end user.

To make it as useful to the user as possible though, enter your FILTER() function on a worksheet, then point your Data Validation to the results of that function. It won't dynamically grow or shrink though like embedding it in the actual List Source box. But it will return a working dropdown for users.

Answer 2

Thanks Ed! Your suggestion to point the Data Validation to the results of the (filter()) function can work.

After posting my question here, I learned of an effective way to use the filter() function's to create a filtered list of values for my drop-down validation cell. The procedure is quite simply, and does works with tables (and if rows are added to the tables, the filter function will pick up the new rows).

To accomplish this,

Step 1: Enter something similar to this into a "helper" cell (E.g. C4) where the result will be dynamically created.

=FILTER(Table1[Close Date], ISNUMBER(Table1[Y Values])*(Table1[Y Values]<>0),"")

Step 2: In the Data Validation settings, Allow "List" and set source to =$C$4#

That's it!

The magic happens because of the hashtag. This hashtag signals that the entire dynamic array's results are expanded. 

Alternatively, use Name Manager to assign the filter formula to a Name. Then put that name (instead of the formula) into the Helper Cell  (e.g. C4).

Note: documenation regarding the use of the hashtag (i.e. #) is located in Excel's the Dynamic Array Help section, under the sub topic "Put basic array formulas to work."

I learned about this technique from this Leila Gharani Youtube video Excel Dynamic Arrays (How they will change EVERYTHING!

Lastly, I feel there is room for improvement by enabling the Data Validation to accept the dynamic array function directly (or via a Name), saving the need for the "Helper Cell" area where the filter function spills its results.

Answer 3

Excellent! I was not aware the # feature worked in Data Validation. I use that in normal functions that refer to other array functions. I'll also have to see if it works in Conditional Formatting, another area that is wonky with Table formulas. 

I love Dynamic Arrays, but it seems they are about 80% baked in with a lot of little issues here and there to still be worked out. Actually working inside of a Table is my biggest desire for them.