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 formulas as I add worksheets is tedious. I need this formula to work for multiple worksheet names, and to determine the worksheet name automatically, based on the value of the adjacent cell. Something like this -- ='Week (variable)'!D4 -- with a variable that points to an adjacent cell with a number in it.
3 Replies
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!!
Riny_van_Eekelen Thank you for the suggestion. I will investigate this function and try it tonight.
