How Can I Force INDIRECT to Update When I Add a New Sheet?

Question

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

Answer 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.