Contents
How to make dynamic column reference in Google Sheets?
To make a dynamic Column reference in Google Sheets Query, you should follow the just above formula where I’ve used Column Numbers as identifiers. See how to do that. If you check the above screen capture, you can understand that I am controlling the column numbers in Query using the cells C9 and F9.
How to identify columns in Google Sheets query?
To identify columns you can use two types of column identifiers in the Select clause in Query. They are column letters like A, B, C or column numbers like Col1, Col2, Col3. I am using the latter to dynamically refer columns in Google Sheets Query. Don’t worry! I’ll explain it later. You can interpret dynamic columns in Query in many ways.
How to control column output in Google Sheets?
These two cells control the column output of Query. Many Google Sheets users think that it’s not possible in Query as the Query Select Clause takes strings as column Identifiers. It’s partially true but there is a workaround. In my example, I am going to control the Query column numbers from outside the formula.
How to use row function in Google Sheets?
If the value in C9 is 3 and F9 is 10, you can use the ROW function to return the number 3 to 10 in row-wise. This is the basic formula. In this formula the number 3 and 9 to be replaced by the values in the cells C9 and F9. To get that we can use the INDIRECT function as below.
How to change columns in Google Sheets query?
The normal solution is to change the data range in Query as below. If your data range is A1: AX, you can use the range like J1: AX to return the columns 10 to 50. Next time when you want to return the columns from 1 to 20, you want to manually change the formula.
How to calculate column number in Google Sheets?
The cell C9 contains the starting column number and the cell F9 contains the ending column number. That means If I put the number 5 in cell C9 and 10 in cell F9, the Query formula would return the columns from 5 to 10.