UDF showing full Path of Worksheet or Worksheet Name?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PDX - 13 Apr 2004 20:37 GMT

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....

Reply to this Message

Frank Kabel - 13 Apr 2004 22:44 GMT

Hi
try something like (if Ref is the range)

ref.parent.name
for the worksheetname

and
ref.parent.parent.fullname

Signature

Regards
Frank Kabel
Frankfurt, Germany

> How can I modify Wallenbachs UDF to show the "Worksheet
> Name" of the referenced worksheet and or the fullpath?
[quoted text clipped - 9 lines]
>
> Thanks....

Reply to this Message

JE McGimpsey - 13 Apr 2004 22:44 GMT

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

> How can I modify Wallenbachs UDF to show the "Worksheet
> Name" of the referenced worksheet and or the fullpath?
[quoted text clipped - 9 lines]
>
> Thanks....

Reply to this Message