FILTER Range with Multiple Conditions – Skip Blank Cells in Google Sheets
Building a dynamic schedule dashboard in Google Sheets.
- Limited to using only functions and formulas, no scripts.
- Have a living table (range A1:M) where each row represents a different event.
- I need to pull multiple rows of data (all text strings) and populate into single column.
- Some columns may be empty (ie. not all events have an Agenda or a Group associated with them).
- Query and some other functions don’t work because they won’t pull the hyperlinks.
The below function works, but I cannot figure out how to get it to skip the blank cells.
=FLATTEN(FILTER($F$2:$M, $B$2:$B=$O$2, $D$2:$D=$Q$2, $E$2:$E=$P$2))
It would be great to add a blank column at the end though that is NOT skipped, so when it returns each row of event details into a column, there is a space between each event.
Here is an example of the table:
|Team A||Optional||Weekly||Monday||Co. Q&A||Info.||hyperlink||website||John|
|Team B||Mandatory||Bi-Weekly||Tuesday||Team mtg.||Info.||blank||website||Amy|
|Team C||Mandatory||Weekly||Monday||Mgr. Sync||Info.||hyperlink||blank||Chris|
Tried IF statements with ISBLANK as well as <>"" but I am not sure if I am putting those in the right place.
The formula below is another variation, which works to populate a separate sheet, but it also does NOT skip blanks and is only pulling the first instance found.
Solution – 1
you could try wrapping your working formula within QUERY()
=QUERY(FLATTEN(FILTER($F$2:$M, $B$2:$B=$O$2, $D$2:$D=$Q$2, $E$2:$E=$P$2)),"Select * Where Col1!=''")