Cell Address in =Hyperlink

[Mike Slattery]

I want to use a cell address as the "link to:", of a =Hyperlink construction.

The cell contents are dynamic and must be constructed from a conconacation of two cells.

Thanks

 

[AnAnalyst]

Hi there,

Can you be a bit more specific about what is in the cell that defines where you want the hyperlink to take you?

e.g.

Is it a cell reference on the same sheet?
A cell reference on a different sheet in the same workbook?
A different file entirely?

This all has a bearing on how you build the hyperlink formula. The Hyperlink function help in Excel 2007 is actually pretty good on this subject.

 

[AnAnalyst]

Apologies - hit the back button and post duplicated

 

[Mike Slattery]

I want to combine a partial hypertext link (linking to a stock info page) that is in cell B20 and the trade symbol for a stock that is in cell B4.

When you use the hyperlink function and attempt to utilize a cell address in place of the hypertext I get an error message that the requested page could not be returned.

The target cell contains the conconacation of the two cells B4 and B20.

 

[AnAnalyst]

Well, I've used this function quite a lot, and it can bit a LITTLE sensitive!

The basic example below replicates a situation similar to what you're describing:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 167px"><COL style="WIDTH: 172px"><COL style="WIDTH: 200px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">MrExcel Message Board</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">http://www.mrexcel.com/</TD><TD>forum/forumdisplay.php?f=10</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A4</TD><TD>=HYPERLINK(B4&C4,"MrExcel Message Board")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

It also depends, however, on the complexity of your string, what exactly it's trying to link to (e.g. a document held on the web page) and often special characters like spaces.

 

[Mike Slattery]

Worked like a charm. You are my new hero! I have been working on this for three days. Thank you,

Michael

 

[ILv2Xlr8]

building on this, what is the hyperlink syntax for linking to a sheet in another workbook?

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD>><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">=hyperlink(??[B1&C1]d1!??,linktext)</TD><TD>file path</TD><TD>file name.xls</TD>><TD>sheet</TD></TR></TBODY></TABLE>

 

[AnAnalyst]

=HYPERLINK("[" & B4 & "]'" & C4 & "'!" & D4,"Find")

Where:

[B4] contains the full path including the filename and extension

e.g. C\Temp\Test.xls

[C4] contains the sheet name

e.g. Sheet1

[D4] contains the cell reference

e.g. A1

All the best

 

[ILv2Xlr8]

Excellent! I would have never guessed that syntax.

=HYPERLINK("[" & B4&C4 & "]'" & D4 & "'!" & E4,"Find")

Can you do the same linking to a numbered item in a word document, e.g. a section, table, or figure?

 

[Andrew Poulsom]

To hyperlink to a bookmark in Word:

C:\Path\Document.doc#Bookmark