UDF showing full Path of Worksheet or Worksheet Name?

  • Thread starter Thread starter PDX
  • Start date Start date
P

PDX

How can I modify Wallenbachs UDF to show the "Worksheet
Name" of the referenced worksheet and or the fullpath?

Function SHEETOFFSETNAME(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSETNAME = .Parent.Sheets(.Index +
offset).Range(Ref.Address).Value
End With
End Function

Thanks....
 
Hi
try something like (if Ref is the range)

ref.parent.name
for the worksheetname

and
ref.parent.parent.fullname
 
One way to modify John WalKenbach's UDF:

Name:

SHEETOFFSETNAME = .Parent.Sheets(.Index + offset).Name

or


Full path and sheet name:

SHEETOFFSETNAME = .Parent.Fullname & "." & _
.Parent.Sheets(.Index + offset).Name
 
Back
Top