Referencing the worksheet name in an excel function

[QuietMan]

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

 

[Charles Williams]

If you want to know the name of the sheet of a range passed as a parameter
use

Rnge.Parent.Name

If you want to know the name of the sheet that contain the call to the UDF
use
Application.Caller.Parent.Name

If you want to know the contents of a cell that contains a sheet name use
Rnge.Value

Be careful that all references to cells are passed as parameters to your
function so that Excel knows to recalculate your function whenever any of
the cells change. Otherwise you will have to make your function Volatile,
which is slow.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com