Extract Data Based on a Drop Down List - Deselect Option

Hi there, I found this spreadsheet containing a drop-down list that extracts data based on the selection in Cell H4 and H6 However, say you selected both filters, is it possible to 'deselect' o...

Extract-Data-from-List.xlsx16 KB

excel

djclements
Nov 15, 2023
exceljunior Another option is to use the IF function with ISBLANK to perform a different calculation if the criteria cells are blank. For example:

=FILTER(Sales, IF(ISBLANK(H4), ROW(Sales), Sales[Branch]=H4)*IF(ISBLANK(H6), ROW(Sales), Sales[Products]=H6), "Nothing Found")

The ROW function in the above formula simply returns the row number for each row in the table, if the criteria cell is blank. The FILTER function interprets all numeric values as TRUE (other than 0, which is interpreted as FALSE), so all records will be returned if both criteria cells are blank.

djclements

Bronze Contributor

Nov 15, 2023

exceljunior Another option is to use the IF function with ISBLANK to perform a different calculation if the criteria cells are blank. For example:

=FILTER(Sales, IF(ISBLANK(H4), ROW(Sales), Sales[Branch]=H4)*IF(ISBLANK(H6), ROW(Sales), Sales[Products]=H6), "Nothing Found")

The ROW function in the above formula simply returns the row number for each row in the table, if the criteria cell is blank. The FILTER function interprets all numeric values as TRUE (other than 0, which is interpreted as FALSE), so all records will be returned if both criteria cells are blank.

exceljunior
Copper Contributor
Nov 16, 2023
Thanks DJ!

Forum Discussion

Extract Data Based on a Drop Down List - Deselect Option

Resources