Sheet Name before NAMED RANGE

[syed_mushraf]

Hi,
I have a huge file containing multiple sheets in it and each sheet have multiple NAMED RANGE that I am using in different calculations etc. I want to know if each NAMED RANGE can show his relevant sheet name before this name it will help me a lot while reviewing it to know which NAMED RANGE is belonging to which sheet. For example

Sheet1 having different name range in it

USA (A10:A300)
UK (B1:B300)
PAK (C1:C500)

I just want to know if sheet name can be picked while I am using these name range as below:

Sheet1 USA
Sheet1 UK
Sheet1 PAK

I don't want to write sheet name in as NAME RANGE it must be dynamic whenever I change the sheet name it should be changed dynamically. Thanks for your support if i can handle it through any logical solution. thanks

 

[Jeffrey Mahoney]

If you want to show the sheet name in a cell then create a named range called GetSheetName and enter this formula:
=LAMBDA(CellRng,TEXTAFTER(CELL("filename",CellRng),"]"))

Use it like: =GetSheetName(Cell)
Where cell can be a single cell on any sheet

The sheet name will change when you change the actual sheet name

 

[syed_mushraf]

=LAMBDA(CellRng,TEXTAFTER(CELL("filename",CellRng),"]"))

Hi Thanks for your reply but I cant get where to put this formula and how the dynamically sheet name to be displayed before every NAMED RANGE. It will help me to know relevant sheet of any NAMED RANGE. Currently I click on named range that jumped me to its relevant sheet and I get to know it. Please help me if there is any other way to display sheet name before the named range dynamically instead of putting it as range name. thanks

 

[Jeffrey Mahoney]

To add a named range, In the Menu: Formulas > Name Manager > New

Are you trying to add hyperlinks in cells so you can click the cell and bring you to another sheet? If so then creating a hyperlink is a static value. The formula I gave you won't be dynamic in a hyperlink

You could try this. There's a little setup work to do, but it works great.
Create a named range on each of your sheets where you want to keep the reference to another sheet. Call the named range "Navigate" and make sure to Create it as a SHEET level named range, not a workbook level. So for each sheet you have a named range called Navigate, make sure to choose that sheet name for the scope.

Next, add a formula similar to this in each of those cells: =GetSheetName(Sheet2!A1)&"!A1"
This uses that other named range I gave you above.

Then you need to add this macro to 'ThisWorkbook' module in VBA. When you double click any of your 'Navigate' cells, it will read the cell, activate the sheet and select the cell address you provided.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Dim i As Range
  Dim Loc As String
  Dim ShtName As String
  Dim CellAddr As String
  
  Set i = Intersect(Target, Range("Navigate"))
  If Not i Is Nothing Then
    Loc = i.Value
    ShtName = Left(Loc, InStr(Loc, "!") - 1)
    CellAddr = Mid(Loc, InStr(Loc, "!") + 1, 100)
    Sheets(ShtName).Activate
    Range(CellAddr).Select
  End If
  
End Sub