Forum Discussion
Formula to calculate worksheet name
I use this formula -- ='Week 104'!D4 -- (and lots of variations of this formula) to determine the value of cells in other worksheets (Week 102, Week 103, Week 104, Week 105, etc.). Updating the formu...
Neale1951 You can build a dynamic reference with the INDIRECT function. Something like in the pictures below.
The formula in B1 on Sheet1 builds a text containing the sheet name and cell reference for the cell from which you want to return the value. The formula is:
=INDIRECT("'Weeek "&A1&"'!A1")
Edit: Obviously, I meant ot use sheet names called "Week nnn", not "Weeek nnn". But it doesn't really matter.
Well, I had to query Bing AI Chat to figure out how to make it work, sorry, and this is what it suggested: =INDIRECT("'Week "&B74&"'!D4"). As I mentioned before, I have a LOT of these formulas to pull the value from different cells on different worksheets (named Week 1, Week 2, Week 3, ... etc.). The "B" column lists the week numbers (1, 2, 3, ... etc.) so now I can edit the formulas "one more time" and never again. Thank you for the help!!