Using filter in dynamic array function

[AurelienSimon]

Hi everybody,

I often use dynamic arrays functions in my Excel reports, especially FILTER ans GROUPBY, with filters.

To do this easier to use, I use drop-down lists, based on UNIQUE functions.

User can change the filter with these drop-down lists.

The problem is I have to select a value in these lists, I can't leave the cell blank.

Is there a method to say to the filter: hey, take all the values, not just one?

Thanks for helping me and my apologies about my approximate English.

Have a nice day.

Aurelien

[#2]

@AurelienSimon A couple of options you might want to try include:

=FILTER(array, (array1=IF(cell1="", array1, cell1))*(array2=IF(cell2="", array2, cell2)))

-OR-

=LET(
    all, SEQUENCE(ROWS(array))^0,
    FILTER(array, IF(cell1="", all, array1=cell1)*IF(cell2="", all, array2=cell2))
)

Please see the attached workbook for examples...

[#3]

Hi @djclements,
Thanks a lot for responding.
I'm not familiar with the LET function but I have to learn it in the coming days.
I'm going to take a look at both of these two formulas in order to understand them properly.
I'll tell you if all is OK.
Have a nice day, thank you very much.
Aurélien