Using the TODAY function, we can dynamically select different columns each day in Google Sheets QUERY. This can be beneficial in two ways:
- Selecting column 5 today, 6 tomorrow, and so forth, by utilizing the dynamic column tweak in the SELECT clause (note: these column numbers are for illustration only).
- Choosing column 1 based on the criteria applied in column 5 today, column 6 tomorrow, and so forth, utilizing the dynamic column tweak in the WHERE clause (note: these column numbers are for illustration only).
Once mastered, this technique allows you to dynamically specify different columns each day in both the SELECT and WHERE clauses of QUERY.
The dynamic column tweak in Google Sheets QUERY becomes particularly useful when incorporating new columns into your table with fresh data. The above dynamic column tweak enables you to view only the latest data without needing to edit the formula daily.
Dynamically Select Different Columns Each Day in the QUERY SELECT Clause
Assuming 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 subsequent columns contain each day’s data. The tab name of the sheet containing the data is “trip list”.
Suppose you have current data in the sixth column (Trip #5). The following QUERY formula will retrieve 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 denotes 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:
=QUERY({'trip list'!A1:DF1000}, "select Col7 where Col1 is not null", 1)Here’s 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)However, this won’t work in your case unless you make the following changes:
- Replace the data
'trip list'!A1:DF1000with your actual data range. - Change the column number 6 to the number you want to start with.
- Change 100 to the last column. You can adjust this number based on the maximum number of columns you expect in your table.
- Replace 45197 with the number you get when you enter
=DATEVALUE(TODAY())in your Sheet. If this formula returns a date or timestamp, apply Format > Number > Number.
Dynamically Specify Different Columns Each Day in the QUERY WHERE Clause
This is a different scenario. Let’s say 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 filter the 7th column the next day?
Similar to the SELECT clause, we can dynamically specify different columns each day in the QUERY WHERE clause as well.
To achieve this, we can replace Col6 in the WHERE clause with the following code:
Col"&MIN(TODAY()-45197+6, 100)&"Note: You should replace 45197 and 100 as instructed earlier.
This will ensure that the formula always filters 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 function serves as the cornerstone for dynamically selecting and filtering different columns each day in QUERY in Google Sheets.
As a volatile function, akin to NOW, RANDARRAY, RAND, and RANDBETWEEN, the DATE function recalculates whenever the spreadsheet changes.
In our formula, 45197 represents the date value of today’s date. You can obtain this value by entering =DATEVALUE(TODAY()) in a blank cell.
When we subtract today’s date from itself, the result is always 0. We then add the desired column number for selection, which in our example is 6.
The following day, the TODAY function updates, causing TODAY()-45197 to yield 1. This forms the basis of the dynamic column selection logic.
Furthermore, we employ the MIN function to cap the formula at the 100th column. Here’s how it operates: MIN(column_number, 100)
This function returns the smaller value between the dynamic column number and 100. Consequently, it ensures that the formula never selects a column beyond the 100th column.
