a quick way to hyperlink 2000 of cells ?

[kontiki]

Goodday,


I have an excel file listing more then two thousand paintings and
their artist.

I now want to hyperlink all cells from column A to jpeg pictures of
these paintings. I know how to do this, but it takes such a long time
to hyperlink
every cell individually.

Is there no way to get this done in a kind of batch like operation, or
is there another program where I can copy my excel database and get
the job done more
easily ?

Any reaction is very much appreciated.

Best regards,


Kontiki

 

[Frank Kabel]

Hi
you could use the HYPERLINK formula in a helper column. how are your
files named

 

[Gord Dibben]

Kontiki

Are you OK with a Macro?

Sub MakeHyperlinks()
'David McRitchie.........originator
Dim Cell As Range
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=Cell, _
Address:=Cell.Value, _
ScreenTip:=Cell.Value, _
TextToDisplay:=Cell.Value
End With
Next Cell
End Sub

Gord Dibben Excel MVP

 

[David McRitchie]

I would probably go with the HYPERLINK Worksheet Formula
suggested by Frank, but I would code it something like
=IF(A2="","",HYPERLINK("http://artbynumbers.com/" & Lower(A2 & "/" & B2) & ".jpg", "[x]"))

where A2 is the artist, and B2 is the title
though you may have to substitute spaces with %20

SUBSTITUTE(B2," ","%20") instead of B2

Just guessing as to what you actually have, not knowing if
you are hyperlinking to a worksheet, cell, web page. Though
I imagine the art is real art and I'm just kidding on a domain name.

the [x] will display rather than the actual link which would be
a heck of a lot longer and very redundant.

Also you would use the fill handle to fill the formula down.
http://www.mvps.org/dmcritchie/excel/fillhand.htm