[Solved] Formulas: refering to worksheet names in forumlas

Try something like this (of course I have no clue where you're putting the formula or where you're getting the sheet name so you'll have to change the code to meet your needs):

Dim SheetName As String
SheetName = ActiveSheet.Name
Sheets("Working").Range("A1").Formula = _
    "IF('" & SheetName & "'!D2="""","""",'" & _
    SheetName & "'!D2)"

Regards,

I do hope this is OK and that offers flexibility for you too:
Post back if you need a little extra .

Kindest Possible regards

Jack in the UK

Code:

Sub Jack_OzGrid()
Dim twb As Workbook
Dim myActiveSheet As Worksheet
Dim mySource As Range
Dim myDestination As Range
Dim myName As String
Set twb = ThisWorkbook
Set myActiveSheet = ThisWorkbook.ActiveSheet 'Sheets("Oct02")
Set mySource = Application.InputBox( _
prompt:="Please Select Source Cell IE D2", Type:=8)
Set myDestination = Application.InputBox( _
prompt:="Please Select a Destination Cell IE A1", Type:=8)
myName = myActiveSheet.Name
If mySource.Value = "" Then
myDestination = "Your Message or Formula is Empty " _
& myName
' *** or if you need to return blank if D2 Empty use this code
' mydestination = ""
End If
If mySource.Value <> "" Then
myDestination = "Your Message or formula if populated " _
& myName
myEnd_Jack_Ozgrid:
End If
Call myKillVar
End Sub

Sub myKillVar()
' Code if needed !!!
Set twb = ThisWorkbook
Set myActiveSheet = Nothing
Set mySource = Nothing
Set myDestination = Nothing
myEnd_myKillVar:
' Some code [if needed]
End Sub