| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
On Aug 17, 2:23*pm, (Terry) wrote:
On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert wrote: = =Terry, = =This can be done in VBA, but you don't need VBA because Excel formulas =will be faster. *Create an anchor cell to hold the Base Hyperlink, =create another anchor cell to hold the file extension, and create a =cell to hold the filename. *Of course, this is assuming you have a =spreadsheet with all of your file names in it. *(If you are wanting to =get the file name list from, say, a folder, then this is a different =issue). *Build the string and then embed the string in the HYPERLINK =function. *For example, see below. = =A1: Base Hyperlink Text =B1: file extension =A2: start of the file names = =A1: E:\folder1\folder2\ =B1: .jpg =A2: B6TYY =A3: next file name =A4: next file name =A5: etc. = =B2: =3D$A$1&A2&$B$1 =B3:B(end): copy the B2 formula down = =C2: =3DHYPERLINK(B2) =C3:C(end): copy the C2 formula down = =If A1 needs the preceeding "///" then add it into the cell. *When you =are ready to switch from E: to C:, simply change cell A1, calculate =the cells, and you're done. = =If everythink looks good and is working, then you can combine both the =HYPERLINK and concatenated formulas into one. = =B2: =3DHYPERLINK($A$1&A2&$B$1) =B3:B(end): copy the B2 formula down = =Best, = =Matt That did it, Matt. Thanks a bunch. I had to delete "3D" fom the expresions to get it running. (B2: =3DHYPERLINK($A$1&A2&$B$1)) I have a feeling it was inserted by Free Agent, my news reader. This is just what I was looking for, and you have saved me untold hours, and errors, of typing!! I think I'll devote some time to learning the syntax required! Might save us all a lot of time... Thanks again! Cheers-- Terry--WB4FXD Edenton, NC Terry, Glad to be of help. Matt |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How Can I Globally Change Hyperlink Addresses? | Excel Discussion (Misc queries) | |||
| How do I change column addresses from numeric to alpha? | Setting up and Configuration of Excel | |||
| How do I make a global change of Hyperlink addresses? | Excel Discussion (Misc queries) | |||
| how to hyperlink a column of fileserver addresses? | Excel Worksheet Functions | |||
| copy/convert column email addresses Hyperlink "mailto:" excel97 | Excel Discussion (Misc queries) | |||
Threaded Mode
