relative hyperlinks

[zoey2]

I am creating an excel sheet for a client and it has many picture names hyperlinked to actual pictures. The problem I have is the I don't know where the client is storing the pictures. So Joe could store them on a network drive and Bill could have them on an external drive and Carol could store them on her c drive. So I need the client to be able to easily change the hyperlinks ( there could be 200 or 300 hyperlinks to pictures so manually changing them is not an option) I would like to have a cell on the excel sheet show the path to the pictures then have either
1. If the path changes automatically change all the hyperlinks or
2. Have the excel sheet save the hyperlinks to only the picture name ie HYPERLINK( "pic.pdf", "picname" ) and when the user clicks on the pictures it appends the path from the cell that contains the path.

Are either of these possible?

 

[FormR]

Hi, welcome to the board!

As an alternative suggestion, you could use the HYPERLINK() worksheet function to generate the hyperlinks - for example:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Path</td><td style=";">c:\program files</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Pic Name</td><td style=";">Hyperlink</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Pic1.pdf</td><td style="text-decoration: underline;color: #0563C1;;">Click here to open Pic1.pdf</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Pic2.pdf</td><td style="text-decoration: underline;color: #0563C1;;">Click here to open Pic2.pdf</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Pic3.pdf</td><td style="text-decoration: underline;color: #0563C1;;">Click here to open Pic3.pdf</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=HYPERLINK(<font color="Blue">$B$1&""&A4,"Click here to open "&A4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[zoey2]

Sorry I'm a little confused. If they click on cell B4 what path to the picture will be used. Joe may want it to use "c:\program files\pic1.pdf" but fred may want to change B1 to say "h:\pics" then when fred clicks on B4 it loads "h:\pics\pic1.pdf"

 

[FormR]

Sorry I'm a little confused. If they click on cell B4 what path to the picture will be used.

Hi, in my specific example the path is stored in cell B1.

EDIT: The formula should be:

=HYPERLINK($B$1&"\"&A4,"Click here to open "&A4)

 

[zoey2]

ADVERTISEMENT

I'm trying it and when I click on B4 I get the open files/folder dialog it is at location "c:\program files" but then I have to find the picture I want to open. It didn't add the file name.

 

[FormR]

it didn't add the file name.

Hi, in my example the file names are in cells A4:A6 - also note the updated formula in post 4.

 

[zoey2]

ADVERTISEMENT

Nevermind I made a mistake. Got it to work!! thanks!!!!

 

[zoey2]

I have one more question for you, I would like to have more than one link in a cell. I realize the only way to do this is to create text boxes but the hyperlink does not work in a textbox. Do you know a work around.

Thanks

 

[FormR]

I would like to have more than one link in a cell

Hi,

Sorry - I don't know if there is a way to do this.