CONCATENATE HYPERLINKS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to pull the data from a specific set of cells into one cell.

Example : =CONCATENATE("",Sheet2!A4," ",Sheet2!A5," ",Sheet2!A6,"")
Sheet2!A4 is hyperlinked to Sheet2!B4
Sheet2!A5 is hyperlinked to Sheet2!B5
Sheet2!A6 is hyperlinked to Sheet2!B6

The resuslts of the CONCATENATE are the results of the proper cell but no
hyperlinks are carried over. Is there a way to carry over the hyperlinks in
this type of situation? Should a user then clicks on one of the pieces of
data they are properly directed to the according cell.
 
The bad news is that each cell gets only one hyperlink--so you couldn't have
multiple hyperlinks in that cell anyway. (You could plop some rectangles over
the cell and assign a separate hyperlink to each of those shapes as a
workaround.)

But if you wanted to retrieve the hyperlink from single cell and assign it to
that cell, you could use a UserDefinedFormula:

Option Explicit
Function myLink(rng As Range)

Application.Volatile

Dim myCell As Range
Dim myURL As String

Set myCell = Application.Caller(1)

If rng(1).Hyperlinks.Count > 0 Then
myURL = rng(1).Hyperlinks(1).SubAddress
myCell.Parent.Hyperlinks.Add Anchor:=myCell, Address:="", _
SubAddress:=myURL, TextToDisplay:=""
myLink = rng(1).Value
Else
myLink = "No Link"
End If

End Function

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=mylink(a1)
 
Back
Top