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 attach a URL 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!