Forum Discussion
Solved
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...
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 I've adjusted your formula and dropdown lists a little and it does what you ask for, though I feel there must be a better way to do it. However, an early morning over breakfast didn't give me the right inspiration just now.
Select the "*" to deselect an option.
- Thanks for your suggestion Riny
