FILTER Range with Multiple Conditions – Skip Blank Cells in Google Sheets

FILTER Range with Multiple Conditions – Skip Blank Cells in Google Sheets

Problem Description:

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:

CalendarEvent TypeCadenceDayTitleDescrip.AgendaGroupPOC
Team AOptionalWeeklyMondayCo. Q&AInfo.hyperlinkwebsiteJohn
Team BMandatoryBi-WeeklyTuesdayTeam mtg.Info.blankwebsiteAmy
Team CMandatoryWeeklyMondayMgr. SyncInfo.hyperlinkblankChris

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.

=iferror(VLOOKUP(CONCATENATE($D$3,$C$8,D$7), DataC!$A$1:$M$160,MATCH($A$11,DataC!$A$1:$M$1,0),0),"")

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!=''")

enter image description here

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.