Using the TODAY function, we can select different columns each day in Google Sheets QUERY. This can be helpful in two ways:

• Select column 5 today, 6 tomorrow, and so on, using the dynamic column tweak in the SELECT clause (these column numbers are for illustration only).
• Select column 1 based on the criteria applied in column 5 today, column 6 tomorrow, and so on, using the dynamic column tweak in the WHERE clause (these column numbers are for illustration only).

Once learned, you can use this technique to select different columns each day in both the SELECT and WHERE clauses in QUERY.

The dynamic column tweak in Google Sheets QUERY will be useful when you add new columns to your table with fresh data. The above dynamic column tweak will help you to view only the latest data without editing the formula every day.

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

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

Assume you have the current data in the sixth column (Trip #5). The following QUERY formula will return the data from this column:

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

Where:

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

The next day, you want the data from the 7th column of the table. You would need to edit the formula as follows, right?

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

Here is how to dynamically select different columns each day in the QUERY function.

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

So the formula will become:

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

But this won’t work in your case unless you make the following changes.

• Needless to say, you should replace the data range {'trip list'!A1:DF1000} with your data range.
• Change the column number 6 to the number you want to start with.
• Change 100 to the last column. You can increase or decrease this number based on the maximum number of columns you expect in your table.
• Replace the number 45197 with the number that you get when you enter =DATEVALUE(TODAY()) in your Sheet.

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

This is a totally different scenario. Assume you want to filter the vehicle numbers that are engaged in transportation.

The following formula will return the vehicles in the first column if the 6th column contains data:

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

How do we select the 7th column the next day?

Similar to the SELECT clause, we can dynamically select different columns each day in the QUERY WHERE clause as well.

To do this, we can replace Col6 in the WHERE clause with the following code:

Col"&MIN(TODAY()-45197+6,100)&"

This will ensure that the formula always selects the column that contains the data for the current day.

The updated QUERY formula will be:

=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 is a volatile function, similar to NOW, RANDARRAY, RAND, and RANDBETWEEN. This means that the DATE function recalculates whenever the spreadsheet is changed.

In our formula, 45197 is the date value of today’s date. We can get this value by entering =DATEVALUE(TODAY()) in a blank cell.

Subtracting today’s date from itself will always equal 0 (zero). We then add the column number that we want to select, which is 6 in our example.

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

We have also used the MIN function to stop the formula at the 100th column. This works as follows:

MIN(column_number, 100)

This function will return the smaller of the two values: the column number or 100. This ensures that the formula will never select a column that is beyond the 100th column.