relative hyperlinks

zoey2

New Member
Joined
Dec 5, 2017
Messages
11
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 5, 2017
Messages
11
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
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)
 
Last edited:

zoey2

New Member
Joined
Dec 5, 2017
Messages
11

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.
 

zoey2

New Member
Joined
Dec 5, 2017
Messages
11

ADVERTISEMENT

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

zoey2

New Member
Joined
Dec 5, 2017
Messages
11
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top