querying range, and filtering for results that match month name and week number

Dapperstache Theatres 0
Viewed 14

https://docs.google.com/spreadsheets/d/10oXUIaL3MXqpJX8YkpCyf7l1q_h_bD6CdkGVRO6bbYE/edit?usp=sharing

I am trying to create a "Month Viewer" tab that queries another sheet (tables) and filters by month from the dropdown list, and also by month week. This is so monthly results are broken into weeks.

Here is my current formula.

=QUERY(TABLES!A4:N368,"Select * Where M = '1' and N = '"&C2&"' ",1)

While it returns data, it doesn't actually appear to filter it by month (N='"&C2&"), nor by week (M='1').

Any suggestions are appreciated.

google-sheets-formula
1 Answers

There are two reasons the QUERY is not functioning correctly.

  1. You currently have the [headers] variable in the QUERY set to 1. This will automatically return the first row in the data range as the heading for your new data. Change that to 0 or delete it from the QUERY and that will fix it.

  2. For whatever reason, the formatting of your original data in M (I believe it is set as 'Plain Text') means the formatting of the QUERY does not need to be wrapped with single quotes. If you replace 'M' with just the M, that will fix the formatting as well.

Because the first two weeks of January also have no LOG data, I've wrapped the formula in an IFNA( ,) to make the format nice within your LOGS BY MONTH report.

=IFNA(QUERY(TABLES!A4:N368,"Select * Where M = 1 and N = '"&C2&"' ",0),)
LASheets0
Related