Formula for creating hyperlinks

[dgfannon]

I have a spreadsheet "Student Data Directory 2013-14.xlsx" where column C is LastName and column D is FirstName. I have folder /Volumes/SHARED/FANNON_JOBS/34899_EHS Directory 2013/Originals/Directory Second Submission/Photo where the files are named "lastname firstname.jpg".

What is the formula that would create a column of hyperlinks to the images in column BP?

Thank you,
-daniel fannon

 

[CROY1985]

try this

=HYPERLINK("/Volumes/SHARED/FANNON_JOBS/34899_EHS Directory 2013/Originals/Directory Second Submission/Photo/"&B2&" "&C2&".jpg")


Got references wrong here, see answer below

 

[CROY1985]

Try this, enter it into BP1, and then copy down as required.

=HYPERLINK("/Volumes/SHARED/FANNON_JOBS/34899_EHS Directory 2013/Originals/Directory Second Submission/Photo/"&c1&" "&d1&".jpg")

 

[dgfannon]

Thank you for the reply. This seemingly creates the proper hyperlink, but when I go to open the link I get an unexpected error.

The hyperlink created with the formula is: [TABLE="width: 53"]
<colgroup><col width="53" style="width:53pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="class: xl66, width: 53"]/Volumes/SHARED/FANNON_JOBS/34899_EHS Directory 2013/Originals/Directory Second Submission/Photo/Students/Abdul-Jalaal Hadiyyah.jpg

[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
The hyperlink created when I manually insert the hyperlink is: Photo/Students/Abdul-Jalaal Hadiyyah.jpg

I don't really need to open from excel as I'm flowing these hyperlinks into another application, but it seems as though i should be able to open them in excel.

 

[CROY1985]

try this...

=HYPERLINK("Photo\Students"&c1&" "&d1&".jpg")

 

[dgfannon]

I tried that as well. I found that on OSX, you need to have everything in the same folder to work properly. Thank you for helping me through the formula. It works once I found the kink on my OS end.

Take care,