referencing sheets by number instead of name in ce

问题:

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.

回答1:

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

回答2:

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

回答3:

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

回答4:

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