Is it possible to use dynamic Sheetnames?

  • Thread starter Thread starter JeffVarol
  • Start date Start date
J

JeffVarol

I have several worksheets with weekly data. I like to be able to rea
cell data from other sheets depending on the name of the current sheet
Example: If the sheet name is 'Week-12', Cell B5 should read fro
'Week-11'!B5 (Week-12 minus 1 = Week-11).
Is there a way of using formulas in link addresses?
Is there a formula which puts the sheetname into a cell as text?

Thanks for your help.

Jef
 
Hi Jeff
try the following:
1. to get the sheetname of the current sheet use
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))

2. Lets say you have put this sheetname in cell A1 use the following
formula to get cell B5 from the previous week:
=INDIRECT("'Week-" & (--MID(A1,FIND("-",A1)+1,2) - 1) & "'!B5")
 
Jeff,

use indirect. e.g.

In cell A1 put =SheetName()
In cell A2 put "A1" as text
In cell A4 put =Indirect(A1 & "!" & A2)

Create a function in the same workbook

Public Function SheetName()
SheetName = Application.Caller.Parent.Name
End Function

If you can't take it from there, post back.

Robin Hammond
www.enhanceddatasystems.com
 
Back
Top