?How To: Create Hyperlink in a Cell that Selects a Dynamic Named Range?

[Default300]

Hi. Quick Question (I hope).

How do you create a Clickable Hyperlink in a Cell that links to / goes to / Selects a Dynamic Named Range?

I have created the Dynamic Named Range.

I have been able to achieve the above with a Static Named Range.
(eg Insert Menu / Hyperlink / Place In This Document / Defined Names)

I have not been able to achieve the above with a Dynamic Named Range.

Previously I was able to link to / select an "UnNamed" Range using a complex formula I found on the web. I don't have the generic version of this anymore. I've tried to make what I've posted here more generic by removing my heavy customisation, but can't guaranteee that I haven't damaged the functionality (eg relative vs absolute refrences) or column letters.

Column A: LookupColumn

Cell A1: "TextValue001"
Cell A2: "TextValue002"
Cell A2: "TextValue003"
...

Column D: ValueToFind

Cell D1: "TextValue527"

Cell B1: =TEXT("#"&CELL("address",INDIRECT("'Sheet1'!A"&MATCH(D1,'Sheet1'!A:A,0))),"Text")

Cell C1: =HYPERLINK(B1,"HyperlinkText")

​

Incidentally, the two formulae were combined into one in the original. I just split them for clarity and because mine were so long.


As always, any assistance would be very welcome!

By the way, this time I am looking for a VBA-Free solution. I don't want to erase the undo history.

Thanks!

 

[Msadiqrajani]

=HYPERLINK("#" & "My_Dynamic_Range_Name")

If you have multiple dynamic range then....

Add a Data validation List with names of range
In this case A2 and
In a hyperlink cell type......=HYPERLINK("#" & A2)