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:
| Calendar | Event Type | Cadence | Day | Title | Descrip. | Agenda | Group | POC |
|---|---|---|---|---|---|---|---|---|
| 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.
=iferror(VLOOKUP(CONCATENATE($D$3,$C$8,D$7), DataC!$A$1:$M$160,MATCH($A$11,DataC!$A$1:$M$1,0),0),"")
CodePudding user response：
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!=''")
