QUERY Columns depending on data validation / multiple different values

Question

I'm trying to create a timetable that can pull data depending on which day is selected as well as the time (period). I have an example sheet: here

I have used this formula with helper columns which works; however, there might be a better solution.

=iferror(QUERY('MASTERLIST 18/19'!A2:G, "select A,B,C,D where G = '"&B1&" "&B2&"' "), QUERY('MASTERLIST 18/19'!A2:G, "select A,B,C,D "))

What I want to be able to do is to be able to select the day and "ALL" to show all the data for that day but I'm not sure how to do that, I've tried a couple of things but I just break the formula, any help would be very much appreciated!

player0 player0 · Accepted Answer · 2019-03-29T13:43:24

the syntax should look like this:

=IFERROR(IF(B2="ALL",
 QUERY('MASTERLIST 18/19'!A2:H, 
 "select A,B,C,D where H contains'"&B1&"' and H contains'"&B2&"'"),
 QUERY('MASTERLIST 18/19'!A2:H,
 "select A,B,C,D where E='"&B1&"' and F='"&B2&"'")), "no data")

QUERY Columns depending on data validation / multiple different values

1 Answers