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

Your second!J5 is formatted as Text. After inputting your formula which is =General!M5&" "&GetURL(General!M5)

Do CTRL+H and put =

in both Find What: and Replace With: boxes and Replace them.

Now, it will be formatted as General and your formula will work.

For doing this in other cells, select your range and REPLACE ALL = with =

Answer 4

Thanks, here is the link: http://1drv.ms/1WMfszH

What I am trying to do is put the text AND its hyperlink from cell general!M5 in cell second!J5 (as well as M4 to J4 and so on, for the entire column), N5 in K5, S5 in P5 and T5 in Q5.

I really appriciate the help!

Answer 5

If you are comfortable, I will suggest that you upload your complete or a cut version of Excel workbook to onedrive and make sure that it doesn't contain any confidential data which violates your organization's policy. If it does contain confidential data, you may look into considering replacing confidential data with dummy data.

To upload to onedrive - 

1. Zip your file.
2. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum.
3. Open the public folder.
4. Click Upload in the top OR drag and drop the file here.
5. After uploading, right click the file and choose share.
6. Click Get a Link.
7. In the option, choose View Only.
8. Create Link.
9. Optionally, you can choose to shorten the link.
10. Copy and paste the link here.