URGENT Hyperlink Help Needed

[SoonerGirl]

BACKGROUND:

- I am using Excel for the first time to produce a printed publication using data-base publishing. The design software is Adobe InDesign using Data Merge. However, the problem isn't with InDesign, its with Excel.
- There are multiple apartment communities and each has photos, floor plans and logos.
- All images (600+) are in the same folder.
- All images are .jpg files
- I'm on a Mac not a PC
- All images are stored in a folder on my desktop

ASSUMPTION:

I'm assuming there is an easier way to create hyperlinks to photos besides the manual "Insert> Hyperlink> Display As> Link to>....." way I am currently doing this. It is taking way too long to do each hyperlink individually.


IMAGE FOLDER PATH:

/Users/melissa/Desktop/APARTMENT_LOCATOR/IMAGES

ImagesFolder.jpg


EXCEL SPREADSHEET IS SET UP LIKE THIS:

ExcelPic.jpg


THERE ARE OTHER IMAGES FOR EACH PROPERTY IN ADDITION TO PHOTOS (FLOOR PLANS & LOGOS):

ExcelPic2.jpg


WHAT I NEED:

- It seems like there should be a way to designate or specify a particular folder in which the computer knows to search for the named file so that you can just name the file to search for but I can't figure it out.
- I would like to be able to add a column or something where I type the path "/Users/melissa/Desktop/APARTMENT_LOCATOR/IMAGES" and then just use the file names in the cell for each image.
- I must also share this file with my web guys so they can use the same information for the website. However, the links will all be to my desktop. I would like to be able to send them the Excel spreadsheet and my image folder BUT would like for them to be able to replace my path (/Users/melissa/Desktop/APARTMENT_LOCATOR/IMAGES) with their own path to the image folder so the links are updated to access their server. Obviously, the links to my desktop will not work for them.


OTHER THINGS TO CONSIDER:
- I'm a designer - not a tech person and have never even opened an Excel file until last week so I am not exactly skilled in how this program works. Please type slowly and use small words I can understand :-)
- I'm a Mac user not a PC user so I understand some of the path info and command info is different
- If there is something specific in a formula (like "link to cell B2 & B3, etc.") does that information still work if the rows are re-ordered, say alphabetically? Because I will continually be adding additional properties and they won't always be in the same row every time. What was in row B may be in row F next week.
- Last but not least... I have looming deadlines and need to figure this ASAP

Thank you, in advance, for any help you can offer!!! I'm really in a fix and would appreciate any help you can give me!!!

[Kyle123]

Have you had a look at the HYPERLINK() function? Seems like it would do what you need

[SoonerGirl]

Yes. That is what I am doing currently but I'm having 2 problems:

1. It takes a LONG time to link each image separately, and
2. I need the link to be accessible (or at least, easily modifiable) by my web guys when I send them the spreadsheet with the zipped images folder. Currently, all the images are linked to a folder on my desktop - which they can't access.

[Kyle123]

Why would it take a long time?

In A1: /Users/melissa/Desktop/APARTMENT_LOCATOR/IMAGES
In A2: image.jpg
In A3: image1.jpg

In B2: =Hyperlink(A1 & "/" & A2)
In C3: =Hyperlink(A1 & "/" & A3)

[SoonerGirl]

Ok.. I misunderstood you. Like I said, I'm an Excel "virgin" and I have no idea what I'm doing!!

So, what happens if the property that is in row A moves to row G? Do I have to go back and change the "=Hyperlink(A1 & "/" & A2)" formula to "=Hyperlink(G1 & "/" & G2)?
And, do I type (/Users/melissa/Desktop/APARTMENT_LOCATOR/IMAGES) in A2, A3, A4, A5, etc?
And, what exactly goes in the corresponding cells to the right? I'm so confused! I swear I'm not a complete idiot, although I feel like one right now!

[Kyle123]

No, if you drag it, the formula should update.

You only need to type it once if you use Dollar signs, you can then drag down so:
In B2: =Hyperlink($A$1 & "/" & A2)
In C3: =Hyperlink($A$1 & "/" & A3)

[SoonerGirl]

Thank you, Kyle. I'm sure this all makes complete sense to you!! :-)

I'm going to mess with it for a minute and see if I can use the info you have given me to make it work. If I'm still completely lost... I'll be back but will let you know either way! Thank you so much for your patience with me!!!!

[Kyle123]

Try this workbook

[SoonerGirl]

Kyle - Thank you so much for your help!! I found out the problem actually was in Indesign/Data Merge. Long story but got it all fixed. Thanks again!!! You're the best! Certainly wouldn't have figured it out without you. :-)