|
|
|
|
|
|
|
|
|
Excel
|
Display the Sheet Name of a Single-Cell Sheet Reference
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2002
|
|
Submitted by:
|
Zack Barresse
|
|
Description:
|
You may sometimes have the desire to always show the sheet name of a cell that is linked to another sheet. This User Defined Function (UDF) is an easy way to do that.
|
|
Discussion:
|
If you have multiple cells referencing another sheet, e.g. =Sheet1!A1, and want to see the sheet name that particular formula is referencing, the old way was to format the cell as text or precede the formula with an apostrophe to display it as text. Now you can keep your formula and just add this UDF to view it. This does NOT go well in complex functions or anything other than single cell references.
|
|
Code:
|
instructions for use
|
Option Explicit
Function GetName(Target As Range)
If Target.HasFormula = True Then
On Error GoTo endMe
GetName = Mid(Target.Formula, 2, WorksheetFunction.Find("!", Target.Formula) - 2)
End If
Exit Function
endMe:
GetName = "Invalid"
End Function
|
|
How to use:
|
- Copy the code above.
- From within Excel hit Alt + F11 to open the Visual Basic Editor (VBE).
- From the VBE menu choose Insert -> Module.
- Paste code in blank window at right.
- Press Alt + Q to quite the VBE and return to Excel.
|
|
Test the code:
|
- Use the function like you would any other Excel function:. =GetName(A1)
|
|
Sample File:
|
GetNameEx.zip 7.47KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 117 times.
|
|
|