1

I am using INDIRECT to reference sheets that I will create in the future (Week 1, Week 2, Week 3...) I use IFERROR to hide the errors in the cells for future weeks where the sheet does not exist yet. The problem I'm running into is that the formula using INDIRECT does not recalculate unless I manually go in and edit it. The result is that even after I add a new sheet, the formula that references that sheet still throws an error as though the sheet does not exist.

I see some solutions for people adding new rows or columns, but I don't believe they will work for adding new sheets. I also saw the idea of using NOW in the formula, but I am concerned that this will affect performance, as I have 750 cells that are needing this fix.

Here's an example: https://docs.google.com/spreadsheets/d/1sLxf-I0w6O_gF0uIhUI3qmeGbbVadN38GHFKF1gpwJc/edit?usp=sharing

0

1 Answer 1

1

Approach 1

Formula in B1

=BYROW(SEQUENCE(A1), LAMBDA(r,
   INDIRECT("'Week "& r& "'!A1")))

In A1 put the current number of sheets. When you add sheets change the number to match the last sheet number.

Approach 2

Formula in B1

=IF(A1, IFERROR(
   BYROW(SEQUENCE(52), LAMBDA(r,
     INDIRECT("'Week "& r& "'!A1")))),) 

In A1 put a checkbox and toggle it off then on whenever you add sheets.

Approach 3

Use Google Apps Script to dynamically return the current list of sheet names.

Example custom function from this 2019 answer by marikamitsos

Invoke using =sheetnames()

function sheetname6s() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
  return out 
}

Approach 4

Share the details of the XY Problem that created your question and the 750 affected cells. It's possible that changing your aporoach could be an option.

1
  • 1
    Finally got around to working on this again. BYROW and LAMBDA worked beautifully! Thank you! Mar 4 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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