referencing sheets by number instead of name in cells

9
votes

Lets say sheet3.name = "d"

Is there a way I could put in a cell on sheet2 the formula =sum(sheet3!b:b) where sheet3 is being substituted with the actual sheet3 name?

I can only get =sum('d'!b:b) to work so far.

I could use VBA for this probably but I'm curious how to do this in a cell so I don't have to run a macro each time.

4
excelvbaexcel-formula

4 Answers

9
votes

If you can use a UDF User Defined Function that will return the sheet name 

Function SHEETNAME(number As Long) As String
    SHEETNAME = Sheets(number).Name
End Function

then a formula like 

=SUM(INDIRECT(SHEETNAME(3) &"!B:B"))

will return the sum from column B on sheet 3.

SHEETNAME(number) returns the sheet name of the number which is index.

So Sheet(1) returns Sheet1, etc

7
votes

Use below formula anywhere in the sheet to get the sheet name - the sheet must have a filename for this to work:

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

You can either reference that cell using Indirect:

=SUM(Indirect("'"&A1&"'!B:B"))

or, if you don't want to have a second cell, you can combine the two formulas into one:

=SUM(INDIRECT("'"&REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")&"'!B:B"))
1
votes

I'm not sure if this is a good idea but it's the first one I could think of.

I would add additional function to your VBA project which will return actual name of your Sheet3:

Function Sheet3Name()
    Sheet3Name = Sheet3.Name
End Function

Next, when you create sum formula of column B:B in Excel cell you need to do it in this way:

=SUM(INDIRECT(Sheet3Name()&"!A:A"))
1
votes

For anyone not concerned with the order of the sheets, the post by Biff here on mrexcel.com works well.

In Excel 2013, go to the Formulas tab in the ribbon and make a defined name:

Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())

Then use a formula like this example:

=INDIRECT("'"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),A3)&"'!A1")

where A3 refers to the index number in a cell in the current sheet, and A1 refers to the location of the value to be retrieved from the other sheet. I.e., in the current sheet, if A3 = 2, then the formula will point to cell A1 in the second sheet of the workbook. I just use a column of index numbers in my current sheet, then drag this formula down and it fills in values from all of my other sheets.

You will need to save as a macro-enabled file (.xlsm).