Google-sheets – Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date

google sheetsgoogle-sheets-named-rangesgoogle-sheets-query

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes.

Is there a way to write these queries so they don't need to be updated every time a column is added or removed?

Is it possible to use named ranges in queries to solve this problem?

Here's an example: If you add a column after 'F', then column 'G' gets pushed to 'H' and the meaning of the formula changes.

=Query(B:J,"select avg(J) group by G")

Related questions

This question is not the same as Using Query with column headers instead of column letters because this one is focused on the use of named ranges.

Best Answer

It's a kind of tricky, but it is possible with a helper Range and some concatenation.

What needs to be done:

Create a named range, COLS, to carry the column letters like this:
```
A  
B  
C  
D  
E  
...
```
Do it in a vertical way as shown.

Assemble the query string like this:

=QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " & INDEX(**COLS**, COLUMN(G1)) )

Related Solutions

Google-sheets – How to make a query with Google Spreadsheet, that checks for a date

I couldn't find the trick for the real date cell to work yet, but you could have a work around if nothing else by adding the the single quote (') before the date in a format: YYYY-MM-DD in the Q4 cell.

That single quote means that the it will be parsed as string.

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];
    }
  }

  // pass result to formula 
  return aResult;  
}

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total

Best Answer

Related Solutions

Google-sheets – How to make a query with Google Spreadsheet, that checks for a date

Google-sheets – How to apply a function to all the rows preceeding the current one in an arrayformula

Related Topic