Reference cell text string with hyperlink

Question

I am creating a database spread across several spreadsheets and I need to reference cells in my main spreadsheet to be displayed in other spreadsheets.

My problem is this: when I reference a cell in the straight forward way, it only copies the formula or value. When I use the =HYPERLINK function, it copies the text and use it as a URL (which of course results in an error).

I searched online for a while and found the following solution: creating a VBA of =getURL.

Public Function GetURL(c As Range) As String

On Error Resume Next

GetURL = c.Hyperlinks(1).Address

End Function

And so I did.

Except it doesn't seem to work for some reason.

The end goal is to be able to copy BOTH the text string AND the URL attached to it. For that I thought of using it like so:

=HYPERLINK(=getURL(A1), [A1])

i.e. using the =HYPERLINK function, which allows for displaying a text string instead of the URL, while giving it the URL via the =getURL VBA function.

Does anyone know how to make this work (either in this solution or a different one that will achieve the same goal)?

Thank you very much!

Answer 1

There is a worksheet named GetURL in your workbook. I can't read the language of your tab name but it shows in VBA window.

There are two choices -

1. Change the worksheet name to something else.

OR

2. Change GetURL to something else in you code. Let's say, it is ExURL

Public Function ExURL(c As Range) As String

         On Error Resume Next

         ExURL = c.Hyperlinks(1).Address

End Function

Then you would need to use ExURL in place of GetURL in your workbook (You can do CTRL+H and replace in entire workbook in one go)

Answer 2

Do following -

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module 
5. Copy paste the below code in this (A better code is at following link. This code extracts even sub-address also - http://eforexcel.com/wp/tips-tricks-111-extract-urls-from-hyperlinks/)

Public Function GetURL(c As Range) As String

On Error Resume Next

GetURL = c.Hyperlinks(1).Address

End Function

6. Now, put following formula=A1&" "&GetURL(A1) )

Answer 3

Glad that this helped you. Request you to mark relevant posts as Answer so that thread can be closed and others can also benefit.

Answer 4

OK never mind, I changed the columns to general settings and used the =HYPERLINK(GetURL(general!M4), general!M4) solution and it worked!

I can't believe the problem was just the cell settings all along!

Thanx a lot for your help!

I REALLY appreciate it, and so will my boss!

Thank you so much!

Answer 5

That's a step in the right direction, but it didn't solve the problem.

Now the text is copied from the original cells, and the URL is copied and placed after the text.

I am looking for a way to keep the text as a hyperlink to the url.