hyperlinks in excel sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kat - 22 Sep 2004 07:56 GMT

Hi,
I am having difficultly automating hyperlink insertion into a
spreadsheet. I need the value of the individual cel to be incorporated
into associated the hyperlink.
Example:
Cell A1 has the value H3001A02-5. The hyperlink(formula?) for this
cell needs to be:
=HYPERLINK("http://lgsun.grc.nia.nih.gov/cgi-bin/pro3?sname=H3001A02-5&val=1","H3001A02-5")

So the value of the cell is embedded into the web address.
I have 15,000 entries in a columns of a spreadsheet, which I want to
be able to associate hyperlinks to, like the example above.
So I need to be able to get excel to automatically construct the
hyperlinks for the other 14999 cells (incorporating the unique cell
values)

Thanks in advance for any advice/ assistance
Kat

Reply to this Message

swatsp0p - 22 Sep 2004 18:37 GMT

I'm not sure this is the easiest way, but it seems to work for me. You have
to construct your hyperlink using various pieces. I am assuming the only
part of the link that changes is the data from A1:A15000. With that said,
first insert a column next to your variable data (B?), then find a couple of
empty cells (doesn't matter where, let's call them C1 and D1, change the
reference in the formula as needed).
In C1, enter http://lgsun.grc.nia.nih.gov/cgi-bin/pro3?sname=
In D1, enter &val=1
Then build your formula in B1 thus:
=HYPERLINK($C$1&A1&$D$1,A1) [note: use of the $ locks the references
when you copy the formula, do not use the $ on references to A1 as you want
those to adjust as you copy]
Proceed to copy this formula down to B15000.
All the cells in column B should now have a link that will use the
corresponding data from the same row in column A

HTH
Good Luck
swatsp0p

> Hi,
> I am having difficultly automating hyperlink insertion into a
[quoted text clipped - 14 lines]
> Thanks in advance for any advice/ assistance
> Kat

Reply to this Message