Links to sheets by number instead of name in cells

Question

Let's say sheet3.name = "d"

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

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

I could use VBA for this, perhaps, but I'm curious how to do this in a cell, so I don't need to run a macro every time.

Answer 1

If you can use a custom UDF 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 amount from column B on sheet 3.

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

So Sheet(1) returns Sheet1, etc.

Answer 2

Use the formula anywhere on the sheet to get the sheet name - for the sheet you must have a file name for this:

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

You can reference this cell using indirect:

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

or, if you do not 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")) 

Answer 3

I'm not sure this is a good idea, but this is the first that I could think of.

I would add an extra function to your VBA project that will return the actual name of your worksheet3:

 Function Sheet3Name() Sheet3Name = Sheet3.Name End Function 

Then, when you create the formula for the sum of the column B: B in an Excel cell, you need to do it like this:

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