Home |
Search |
Today's Posts |
#1
|
|||
|
|||
a quick way to hyperlink 2000 of cells ?
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 |
#2
|
|||
|
|||
Hi
you could use the HYPERLINK formula in a helper column. how are your files named -- Regards Frank Kabel Frankfurt, Germany "kontiki" schrieb im Newsbeitrag m... 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 |
#3
|
|||
|
|||
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 On 7 Dec 2004 11:40:55 -0800, (kontiki) wrote: 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 |
#4
|
|||
|
|||
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On 7 Dec 2004 11:40:55 -0800, (kontiki) wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't move a named cell without breaking a hyperlink to that cell | Links and Linking in Excel | |||
linking cells in Excel 2003. How to not truncate to 255 characters. | Excel Discussion (Misc queries) | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) | |||
Averaging only cells with data | Excel Discussion (Misc queries) | |||
Cells losing formatting | Excel Discussion (Misc queries) |