Google Sheets QUERY: Select Different Columns Each Day

Using the power of the TODAY function, you can now select different columns each day in Google Sheets QUERY. This clever trick offers two major advantages:

• You can choose a specific column today and a different one tomorrow by using the dynamic column tweak in the SELECT clause.
• You can select a column based on criteria applied in another column today and a different column tomorrow, using the dynamic column tweak in the WHERE clause.

Once you master this technique, you’ll be able to effortlessly select different columns each day in both the SELECT and WHERE clauses in QUERY.

How to Select Different Columns Each Day in the QUERY SELECT Clause in Google Sheets

Imagine you have a fleet of 40-tonne loading capacity trucks, and you maintain a Google Sheet with trip details for each day. Column A contains vehicle numbers, and the subsequent columns contain data for each day. The tab name of the sheet containing the data is “trip list.”

Let’s assume you have the current data in the sixth column (Trip #5). To retrieve data from this column, simply use the following QUERY formula:

=QUERY({'trip list'!A1:DF1000}, "select Col6 where Col1 is not null", 1)

Here’s what the elements of the formula mean:

• {'trip list'!A1:DF1000} represents the QUERY data range.
• "select Col6 where Col1 is not null" is the query.
• 1 indicates the presence of a header row.

To get the data from the 7th column of the table the next day, you would usually need to edit the formula. However, there’s a more efficient way!

Instead of using Col6, replace it with Col"&MIN(TODAY()-45197+6,100)&". This dynamic column tweak will automatically select the correct column every day, based on the current date.

So the formula becomes:

=QUERY({'trip list'!A1:DF1000}, "select Col"&MIN(TODAY()-45197+6,100)&" where Col1 is not null", 1)

To make it work for your sheet, you need to make a few changes:

• Replace {'trip list'!A1:DF1000} with the relevant data range from your sheet.
• Change the column number (6) to the number you want to start with.
• Modify 100 to the last column number you expect in your table.
• Replace 45197 with the result of the formula =DATEVALUE(TODAY()) in your sheet.

How to Select Different Columns Each Day in the QUERY WHERE Clause in Google Sheets

Now, let’s consider a different scenario. Suppose you want to filter the vehicle numbers that are engaged in transportation.

To return the vehicles in the first column if the 6th column contains data, use the following formula:

=QUERY({'trip list'!A1:DF1000}, "select Col1 where Col6 is not null", 1)

To select the 7th column the next day, use the same dynamic column tweak in the WHERE clause:

Replace Col6 with Col"&MIN(TODAY()-45197+6,100)&" in the formula:

=QUERY({'trip list'!A1:DF1000}, "select Col1 where Col"&MIN(TODAY()-45197+6,100)&" is not null", 1)

Formula Logic

The TODAY date function is the key to selecting different columns each day in QUERY in Google Sheets. It’s a volatile function that recalculates whenever the spreadsheet is changed.

In our formula, 45197 represents today’s date, which you can get by entering =DATEVALUE(TODAY()) in a blank cell.

By subtracting today’s date from itself, we always get 0. Then we add the column number we want to select, such as 6 in our example.

The next day, the TODAY function updates, and TODAY()-45197 becomes 1. This is the logic behind how the dynamic column selection works.

To avoid selecting columns beyond the 100th column, we use the MIN function. It compares the column number with 100 and returns the smaller value, ensuring that the formula never selects a column beyond the 100th column.

So there you have it! With the dynamic column tweak, you can effortlessly select different columns each day in Google Sheets QUERY. Give it a try and save yourself the hassle of manually editing formulas every day.

For more useful Google Sheets tips and tricks, visit Crawland.com!