1

I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following:

There are a few cells with formulas like this:

=SUM('Mon-D:Sun-D'!$DN$209)

Where among the many sheets, some are named Mon-D and Sun-D. But it isn't working - instead I have the error #REF!

Error: Unresolved sheet name 'Mon-D:Sun-D'.

So the next thing I did was wrap the names in quotes as I thought maybe it was getting confused:

=SUM("'Mon-D':'Sun-D'"!$DN$209)

or

=SUM('Mon-D':'Sun-D'!$DN$209)

But both give me Error: Formula parse error. I'm not very familiar with Excel, or Google Drive - what am I doing wrong? I'm guessing I need to separate the sheet names more clearly somehow.

2

Maybe:

=sum('Mon-D'!$DN$209,'Tue-D'!$DN$209,'Wed-D'!$DN$209,'Thu-D'!$DN$209,'Fri-D'!$DN$209,'Sat-D'!$DN$209,'Sun-D'!$DN$209)
1

As of September 2019, Google Sheets doesn't support 3D references like 'Mon-D:Sun-D'!$DN$209.

One alternative it's to replace the 3D reference by regular (or 2D ) references. In the specific case of SUM, as was shown on the previous answer, add a reference for each of the sheets.

Another alternative is to create a custom function by using Google Apps Script.

/**
 *
 * @param {string} reference Reference a text value, i.e. "A1"
 * @param {number} start Index base 0 of the starting sheet
 * @param {number} end  Index base 0 on the ending sheet
 * @customfunction
 */
function sum3D(reference, start, end){
  var sheets = SpreadsheetApp.getActiveSpredsheet().getSheets();
  var sum = 0;
  for(var i = start; i <= end; i++){
    sum += sheets[i].getRange(reference).getValue();
  }
  return sum;
}

Use example

=sum3D("DN209",1,7)

The above formula skips the first sheet (index 0) and sum the value of DN209 of sheet index 1 to sheet index 7.

Related

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.