Hyperlink - Functionality lost when sheet saved as new workbook

[Nutters_Inc]

Hyperlinks are clearly not working for me, looking for some direction.

I have an Excel tracker I use for material purchases.

The file has headers of key dates of events, some are document names. Each row relates to a individual material request.

To track all documents, I have created hyperlink to SharePoint (that act as repository for the document storage.)

This gives me a one stop solution for tracking all requests.

Some of this information is sensitive, while others would be suitable for general use.

The information now available is empowering staff to improve efficiencies by tracking live progress of materials and aligning these up to labour required to use the materials.

This is new tracker for the business and I’m starting to feel the pain of getting too many requests for updates.

What I would like to do is to pull out a simplified set of the date, void of the sensitive details and make available for all. For this to be self-refreshing and therefore linked to live details. End users still need to be able to search and/or re-sort the data to meet their requirements.

Such a simple process. All working fine. My stumbling issues is around the hyperlinks. Can not find a solution that maintains them.

Having looked online for a solution, clearly, I'm not able to ask the right question, as all answers found so far are not able to give me a solution for how to code the hyperlinks from details held within another workbook. Think I'm missing something basic but vital. Would appreciate anyone suggestions of a fix.
Stay safe.

 

[jkpieterse]

Did you add the hyperlinks by the usual Insert, Hyperlink (or control+k)? If yes, I would advise to stop doing that and to start placing the hyperlink urls in cells as plain text. You can then use the HYPERLINK function to create a live link using the text in the cells.

 

[felixstraube]

Hi,
So i created 2 files in sharepoint:

Then I opened them both in the desktop app. In workbook Hyperlink01 i added some data

And in workbook Hyperlink02 I referenced it selecting the range in the first one.

After saving and opening up the workbook again it is replaced with the full URL:

Is this what you need?
If you need to create hyperlinks to open up a workbook, you could use that same URL I think (which would be created in your files)

Of if I had a hyperlink to the other file it creates automatically the whole URL:

Is some of these things not working for you?

 

[Nutters_Inc]

Did you add the hyperlinks by the usual Insert, Hyperlink (or control+k)? If yes, I would advise to stop doing that and to start placing the hyperlink urls in cells as plain text. You can then use the HYPERLINK function to create a live link using the text in the cells.

Thanks for the reply.
I've been inserting the Hyperlink, with the cell displaying the friendly name. In my case the friendly name is the document reference number. (quote number or PO number.)
As I use the document name to search. If I'm chasing an order, I need to be able to see and have searchable the quote or PO number.

 

[Nutters_Inc]

Hi,
So i created 2 files in sharepoint:

View attachment 115077

Then I opened them both in the desktop app. In workbook Hyperlink01 i added some data
View attachment 115087

And in workbook Hyperlink02 I referenced it selecting the range in the first one.

View attachment 115086

After saving and opening up the workbook again it is replaced with the full URL:
View attachment 115090
Is this what you need?
If you need to create hyperlinks to open up a workbook, you could use that same URL I think (which would be created in your files)

Of if I had a hyperlink to the other file it creates automatically the whole URL:

View attachment 115091


Is some of these things not working for you?

Thanks for the reply.
I can create a working solution within another sheet held within the same workbook. That all works fine. The issue I have is I need to make this sheet available for all users, am doing this by copying the worksheet to a new workbook and saving this as a new file. When I do this the hyperlinks fail to function.
my formula for the link to the hyperlink is
=IFERROR(IF((OrderRequests!AA51)="","",(HYPERLINK(url(OrderRequests!AA51),OrderRequests!AA51))),"no Link -"&TEXT(OrderRequests!AA51,"dd-mm-yy"))
OrderRequests!AA51 contains the text of the date the delivery was received, and has a hyperlink to the delivery note saved within SharePoint. Have created a formula that will display a 'No lInk - ' text with the date, on any occasion where no hyperlink in in the source worksheet.

 

[felixstraube]

What exactly do you have in OrderRequests!AA51?

Is the "url" function you are using a UDF? I don't have that function in my excel.

Another thing. What version of excel are you using?

 

[jkpieterse]

You can have that searchable by adding another column with the friendly name, to use in the HYPERLINK function which has two arguments...

 

[Nutters_Inc]

What exactly do you have in OrderRequests!AA51?

Is the "url" function you are using a UDF? I don't have that function in my excel.

Another thing. What version of excel are you using?

Thanks for the reply. In OrderRequests!AA51 is a date, with a hyperlink to the delivery document.
Yes I have used a UDF, as below.
Function GetURL(cell As Range) As String
GetURL = cell.Hyperlinks(1).Address
End Function
Am using Microsoft365

 

[felixstraube]

can you show us the content of OrderRequests!AA51. Painting over the sensitive information.
And show us what does your Url UDF return?

 

[Nutters_Inc]

You can have that searchable by adding another column with the friendly name, to use in the HYPERLINK function which has two arguments...

What I've now also tried is to make copy of the sheet within the same workbook. The hyperlinks work as you would expect.
But if I create a copy of the sheet and have it created as a new Workbook, none of the hyperlinks work, but the formulas are showing the right path. Both samples are below.

=IFERROR(IF((OrderRequests!AA105)="","",(HYPERLINK(url(OrderRequests!AA105),OrderRequests!AA105))),"no Link -"&TEXT(OrderRequests!AA105,"dd-mm-yy"))
The above formule works as a hyperlink

=IFERROR(IF(('[__Materials Tracker.xlsm]OrderRequests'!AA105)="","",(HYPERLINK(url('[__Materials Tracker.xlsm]OrderRequests'!AA105),'[__Materials Tracker.xlsm]OrderRequests'!AA105))),"no Link -"&TEXT('[__Materials Tracker.xlsm]OrderRequests'!AA105,"dd-mm-yy"))
This formula failed as a hyperlink, error message of 'The address of this site is not valid. Check the address and try again.