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

Right, so I'm sorry to bring it up again, but it seems that for some reason the success doesn't transform to the actual document, it only works in the database I used for the public folder...

It doesn't work even when I copy/paste the exact code and change the cell name...

I have no idea why it doesn't work anymore.

Now instead of the link it comes out as #NAME? which looks like a hyperlink but nothing happens when I press it.

I have double and triple checked all the cell names and locations and they are all correct:

=HYPERLINK(GetURLכללי!M4), כללי!M4)

Something doesn't work there.

Answer 4

Right, so I'm sorry to bring it up again, but it seems that for some reason the success doesn't transform to the actual document, it only works in the database I used for the public folder...

It doesn't work even when I copy/paste the exact code and change the cell name...

I have no idea why it doesn't work anymore.

Answer 5

Done :)

Thanx again!