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

Hi

Thank you, but that doesn't seem to work either I'm afraid (assuming that you meant copying this function "as is" i.e. to copy as =A1&" "&GetURL(A1) )

This is the exact function as written in my Excel:

=כללי!M5&" "&GetURL(כללי!M5)

Answer 4

Please see whether you are looking for below -

= A1&" "&GetURL(A1)