External reference is losing the Hyperlink from original cell

[funkyman]

I have a reference worksheet with three sheets, in one sheet I have a column full of document names, the same column also contains the Hyperlink to the document in SharePoint as shown below (except in Excel it is in one column called Name).

When I link the cell from the target worksheet I only get the Name, not the URL. Is there a way to copy both in the same cell?

(Note: The target worksheet is a Pivot table which allows the user to view the list of documents in each project folder, with status etc.)

Currently the users have to manually find the document in SharePoint but having the Hyperlink would speed up the process.

Thanks
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]URL
[/TD]
[/TR]
[TR]
[TD]PerformanceTest.PDF
[/TD]
[TD]https://share.company.net/sites/projects/ProDocs/645/PerformanceTest.pdf
[/TD]
[/TR]
</tbody>[/TABLE]