How to insert a bunch of links to an excel document?

September 4, 2012 2:21 PM

How to batch insert links to an excel document?

Ok so here we go:
So I have a pretty nifty website for my little landscaping comany, in that website there are individual plant profile pages for plants that we use in our practice.
In our bids and designs I provide the bid in an excel form (+exported to pdf for folks that prefer that format.)
I want to allow clients to click on the plant name in the bid and be forwarded to the appropriate plant page on our website. Doing it manually (by inserting a hyper link in excel) takes wayyy long than it should. Any ideas?


My current process:
1. Use the landscape design software, export a csv file for the plant list.
2. Open my excel bid form, copy paste the information from the csv file to the bid form under the plants used column.
3. Manually insert hyper links for plants used to their link on my website.

Surely there is some kind of easier method, some batch link inserter?

Thanks in advance for any insights!

posted by Sentus to Computers & Internet (1 answer total)

In your bid form workbook, have a sheet that contains a table with two columns - the first being plant names and the second being the matching links to your website (ensure that the plant names are in the format exported from the design software). Then you can do VLOOKUPs on that table to get the links that you want. Wrap the VLOOKUPs in the HYPERLINK function to use the plant name as the hyperlink text.

So, if your plant names are in column G (starting at row 2) of your main sheet and your lookup table is columns A and B of a sheet called sitelinks (going down to row 50), then in column H of your main sheet you could have

=HYPERLINK(VLOOKUP(G2,sitelinks!$A$1:$B$50,2,FALSE),G2)

And then copy and paste that down to all of the rows that you need it for.
posted by MUD at 2:53 PM on September 4, 2012 [1 favorite]