SOLVED

Use a cell to refer to a tab

Copper Contributor

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 :) 

 

 

 

2 Replies
best response confirmed by Alicia_Racine (Copper Contributor)
Solution

@Alicia_Racine 

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.

S1149.png

Thank you!
1 best response

Accepted Solutions
best response confirmed by Alicia_Racine (Copper Contributor)
Solution

@Alicia_Racine 

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.

S1149.png

View solution in original post