Display cell contents with hyperlink from different worksheet

[carbfrze]

Hello all,
Been using Excel 2016 for many years now. I have a large workbook that I use for my department training checklists. I have a mater check list that I use to populate for all employees training. The document has multiple worksheets that has macros, text, and hyperlinks in some cells. I currently use cell references to pull in the cell contents from the master checklist for the text cells only but would really like to be able to pull in the cells contents that have hyperlinks. For the text cells I currently use the formula ='Sheetname'!A1 which works great for the cells that have text only. That formula works great except for any cell that has a hyperlink it is only displaying the text and does not pull in the actual hyperlink. When I try to use the formula above it only pulls over the "friendly name" / text that is typed in and not the hyperlink that is in the cell.

Any ideas on how this can be done?

 

[Sufiyan97]

Post your sample data with some expected results with below link

 

[carbfrze]

I'm on a work pc and cannot install add-ins without permission. Hopefully my attached images will explain it better. Basically on Sheet 2 I would like to use the ='Sheet1'!A2 to be able to display the content from Sheet1 including the hyperlink. As of now it just pulls in the text and not the hyperlink. No matter if the original cell is set as a hyperlink typed out or by using a hyperlink inserted using CTRL+K and using a name.