How to create a hyperlink to another sheet within same excel -BIP report Excel template

Summary:

I am trying to create an Excel template in which there are 2 tabs. One will have budget control balance details and other sheet will have PO details. Expected functionality is 'If we click on resource name in sheet1, it should navigate to first instance of same resource name in sheet 2.

Content (please ensure you mask any confidential information):

I used below syntax, but it is not allowing me to dynamically print the resource name

--1# In the sample template, I have used the syntax =HYPERLINK("#'Sheet2'!$A$3","Somewhere in another worksheet") to create hyperlink to another sheet.
It works but it is overriding the actual field value of <?RESOURCE_NAME?>. I want resource value to be displayed, and it should have hyperlink which points to another sheet.