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

Thanx, but I'm afraid this doesn't seem to be working.

Input: =General!M5&" "&GetURL(General!M5)

Output: #NAME?

It is displayed as a URL (blue with an underline) but doesn't act as one (mouse over doesn't turn to pointing finger).

p.s. the module seems to have been inserted correctly, it is displayed in the list of functions when I start typing it's name and I choose it from the list.

Answer 4

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

Output: 

#NAME?

כללי!M5 content (including hyperlink): 

Post-trauma<br> administration of the pifithrin-α oxygen analog improves histological and functional outcomes after experimental traumatic brain injury

Thank you! :)

Answer 5

What is the output are you getting and what is expected output....I also need to know what is in A1..