Google Sheets QUERY: Passing Cell Data in SQL string literal. ( WHERE [Cell data] Contains [Cell Data] )

Question

I am making an automated roster selector called "Bathroom Tracker" in Google sheets.

I have one sheet "Master Sheet" with a matrix of Student names (Anonymized), T/F check Boxes, and their schedule. Columns are the class period, Cell data is the teacher (Anonymized).

Master Sheet Screenshot

I have a second sheet, "Filtered Sheet", where two drop downs are used to search for Teacher X's students in Y period.

Filtered Sheet Screenshot

Here is a link to a dummy data version: https://docs.google.com/spreadsheets/d/1Yy8F849TXrXvGsvWyeOuslsxTidNL60CFToQJrq0UEk/edit?usp=sharing

I am using the QUERY function, asking for it to select a range of columns if the cell data at [period] contains [teacher].

Here is the current cell function: =QUERY({'Master Sheet'!B3:V184}, "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 WHERE '"&T(C3)&"' CONTAINS '"&T(B3)&"' ")

If I replace the [Period] dropdown with the text "Col13", I get a working example: Working Example Screenshot

...But replacing the text in the period dropdown with data like "Col13" does not produce the same effect.

Any help in this would be much appreciated! Thank you!

Mr.A seems to be listed only under Advisory and not 1st?! Can you manually enter the expected output in your test sheet — rockinfreakshow
– rockinfreakshow, Commented Oct 29, 2023 at 18:01

Tedinoz · Accepted Answer · 2023-10-30 23:15:02Z

You want to create as list based on QUERY that relies on two dropdown values:

Teacher name
Class Name

Your formula is:

=QUERY({'Master Sheet'!B3:V184}, "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 WHERE '"&T(C3)&"' CONTAINS '"&T(B3)&"' ")

The main problem with the formula is that "&T(C3)&"' (which returns a class name) should return a column number

Answer

Step1:

Create a reference range for the Class names, and include the relevant "Column number" in an adjacent cell
Create a similar reference list for teacher names
try this formula: =query({'Master Sheet'!B3:V184}, "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 WHERE Col"&join("",vlookup(C3,assumptions!$A$3:$B$11,2,0))&" contains '"&B3&"' ",0)

Logic

WHERE Col"&join("",vlookup(C3,assumptions!$A$3:$B$11,2,0))&"
- C3 is the DropDown value for the Class
- assumptions!$A$3:$B$11 is the Data Validation range with the relevant column number in an adjacent cell.
- vlookup takens the value in Cell C3 and returns the columns number
- Col"&join(... returns a string value "Col13", or whatever class was selected.
contains '"&B3&"'
- B3 returns the Teacher name

Assumptions

Results

Collectives™ on Stack Overflow

Google Sheets QUERY: Passing Cell Data in SQL string literal. ( WHERE [Cell data] Contains [Cell Data] )

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related