Link to named range from adjecent cell value

[Grege]

Hello all,

After firghting with this for over an hour I am coming to the board, I 2 lists of data making a 3D matrix....in sort i want the cell values from one to link to the range in the other.

I have used for a long time =HYPERLINK("file:///\\BTNNA001\ZDATEN\Sicherheit_Umwelt\Betriebsanweisungen\"&C11&".pdf";C11) to turn cell values into links to files but never to link to a range. I have also tried a couple of macros i know and linking to the file is OK but the range is always the problem!

So is this possible to link a range with a hyper link based on a cell vaule with a formular/macro?

Data is as follows

the ranges are named as per data in "muster_Platz" and naturally i need the link to be in the "link" column

<TABLE style="WIDTH: 221pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=295 border=0 x:str><COLGROUP><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 114pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=152 height=34>Link</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Muster_Platz</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=152 height=34> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>WIG_Schweißplatz</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=152 height=34> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Vorbehandelungwaschanlager</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=152 height=34> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Vorbehandelungwaschanlager</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Trockner</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Trockner</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #eae6dd; BORDER-TOP: #eae6dd; BORDER-LEFT: #eae6dd; WIDTH: 114pt; BORDER-BOTTOM: #eae6dd; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=152 height=17></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Trockner</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Trockner</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Trockner</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Pulverbeschichtung</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; mso-ignore: style; mso-pattern: auto none" width=152 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Pulverbeschichtung</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=152 height=34> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Prüfstand_Herde</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; mso-ignore: style; mso-pattern: auto none" width=152 height=34> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=143>Prüfstand_Herde</TD></TR></TBODY></TABLE>

Cheers for the help

Greg

 

[Andrew Poulsom]

Are those the names of ranges in the same workbook? If so try:

=HYPERLINK("#"&B1)

 

[Grege]

thanks, once again helped a lot however my office collegues were a bit concerend when i said 'i love you' to the computer...

In my hope to understand things a bit better... the # means "refference in sheet"??? and if so does it always mean that?

 

[Grege]

Andrew,

further info...with your help i managed to edit a mcro to do the job

Sub Insert_hyperlink()
Dim c
Dim FolderName
'insert file location here'
FolderName = "#"
'C is the cell that is highlighted'
For Each c In Selection
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= _
FolderName & c.Value & "", TextToDisplay:=c.Value
'foldername (the name from above) cvalue (text in the cell) and in" the file extention"'
Next c
End Sub

cheers again and look forward to learing about #

Greg

 

[Andrew Poulsom]

The # means same document (the workbook that contains the formula).