Forum Discussion
Use a cell to refer to a tab
Hello,
I am adding cell H26 of tabs named 1,2 & 3 using the following formula:
=SUM('1:3'!H26)
Instead of the number 3 in the above formula, I would like to be able to reference a cell to give me the tab name. That way if I add tabs I can easily update all the formulas.
Thanks 🙂
On another sheet, format a column as Text.
Enter the names of the sheets (such as 1 2 and 3) in cells in this column.
Select the names.
Click in the Name box on the left hand side of the formula bar.
Enter SheetName and press Enter. This assigns the name SheetName to the list of names.
Format two other cells, e.g. C1 and D1 as Text too.
Select C1 and name it First.
Select D1 and name it Last.
Enter the name of the first sheet to include in C1, and the name of the last sheet to include in D1.
You can now use the formula
=SUM(INDIRECT("'"&INDEX(SheetList,MATCH(First,SheetList,0)):INDEX(SheetList,MATCH(Last,SheetList,0))&"'!H26"))
If you don't have Microsoft 365 or Office 2021 confirm the formula with Ctrl+Shift+Enter.
As you change the values of the First and Last cell, you'll see the result of the formula change.
On another sheet, format a column as Text.
Enter the names of the sheets (such as 1 2 and 3) in cells in this column.
Select the names.
Click in the Name box on the left hand side of the formula bar.
Enter SheetName and press Enter. This assigns the name SheetName to the list of names.
Format two other cells, e.g. C1 and D1 as Text too.
Select C1 and name it First.
Select D1 and name it Last.
Enter the name of the first sheet to include in C1, and the name of the last sheet to include in D1.
You can now use the formula
=SUM(INDIRECT("'"&INDEX(SheetList,MATCH(First,SheetList,0)):INDEX(SheetList,MATCH(Last,SheetList,0))&"'!H26"))
If you don't have Microsoft 365 or Office 2021 confirm the formula with Ctrl+Shift+Enter.
As you change the values of the First and Last cell, you'll see the result of the formula change.
- Thank you!
