Feb 05 2024 10:22 PM
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
Feb 06 2024 12:15 AM
@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...
Feb 06 2024 09:45 AM