QUERY using cell contents as SQL variables

Question

Here is what the spreadsheet I'm working with looks like:

Summary table

(top of sheet)

Source data

(same sheet, below output)

I'm using the QUERY function to populate the appropriate feeds in the summary table with the data starting at A24 needs to be placed into.

Here is the formula I'm using in cell C6 (similar formulas are used throughout the summary table):

=QUERY($A$24:$D$57, "Select D Where B='ENQ' and A='2/27/14 - Thu'")

This gets the right information, but the formula needs to be edited to be unique in each cell it's used in. The problem being unable to quickly populate the cells with A='2/27/14 - Thu' being too specific.

I was trying to set it up so that:

• date in A would compare with dates found on headers in ROW 2 before accepting data
• room type in B would compare with value from A in each row of the summary table

How can the QUERY function be written to refer to these values as variables, instead of using the literal strings in my original function?

Solution

1. Instead of fixed strings such as 'ENQ', you can have your formula refer to the index in column A of your output. For example, you could change your formula to this, in cell C4:

   =QUERY($A$24:$D, "Select D Where B='" & $A4 & "' and A='2/27/14 - Thu'")
                                            ^^^^^^^
   

   The ampersand (&) is used to concatenate string segments.

   Note that since the source data extends to the bottom of the data range of the sheet, we can forego specifying the bottom row. The range $A$24:$D will take in all rows, so it will automatically adjust to additional source data.

2. To compare dates, both values need to be dates. "2/27/14 - Thu" is not recognized as a date in your source data sheet, but as text, even though you've set the numeric format to date. So make sure you change all your source data to have actual dates in column A.

   You can still have your dates formatted the way you like - see this screenshot. The content of A2 is now a proper date, "2/27/2014", but the format is set to display "mm/dd/yy - DDD". As long as the data is a date, the query can be built using the spreadsheet TEXT function to interpret the date in its yyyy-mm-dd format.

   

3. With dates in your source column, you can use todate() scalar function to tell QUERY to convert the date in the source to a query-date, and the date keyword to treat the following literal string (from C2) as a date.

   The resulting function for cell C4 is:

   =QUERY($A$24:$D , "Select D Where B='" & $A4 & "'and todate(A) = date '" & TEXT($C$2,"yyyy-MM-dd") & "'")
                                                           ^^^^^^      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   

   Similarly, for D4:

   =QUERY($A$24:$D , "Select C Where B='"&$A4&"'and todate(A) = date '"&TEXT($C$2,"yyyy-MM-dd")&"'")
   

4. Your calculations in column E can be improved to handle #N/A results appearing in columns C or D:

   =if(isna(C12*D12),0,C12*D12)