Data range using a string as a reference (a dynamic sheet picker)

Question

I would like to pull info from one sheet at a time depending on what is present in a certain cell (preferably from a drop-down) let's say A1 .

In A1 on a drop-down I would like the names of the sheets in the current workbook + data range (this could be for example A1:B10).

In A1 = Sheet2 A1:B10 this is a full example (as a string )

The formula will be in A3 for example

If it was a normal reference it would look something like this =SUM('Sheet2'!A1:B10) What I'm looking for would work like this =SUM(A1) But I can imagine it would actually look something more like this =SUM("A1") or if it was a query something more along the lines of =QUERY ("A1" ,"SELECT A, B") rough syntax

Hope this makes sense and someone can help, thanks in advance

Ps if it can't be done without script that's fine just wondering

Ritesh Nair Ritesh Nair · Accepted Answer · 2017-08-03T20:19:12

You can try the below code....

//This will add all the sheet Names and the range in the cell A1 as dropdown 
  function addDropDown(){
      var cell= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1");
      var sheet= [];
      var allsheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
        for( i = 0; i < allsheets.length; ++i){
           if(allsheets[i].getName().toString().toLowerCase().indexOf("report")!=-1){
             sheet.push(allsheets[i].getName()+"!"+allsheets[i].getDataRange().getA1Notation());
           }
        }
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(sheet).build();
      cell.setDataValidation(rule);
    }

The only issue is you can't use =sum(A1) directly but instead you can do this =sum(INDIRECT(A1)) or =QUERY(INDIRECT(A1),"SELECT A, B")

Data range using a string as a reference (a dynamic sheet picker)

1 Answers