Need to look up data from a hyperlink

[PatrickJ@GoldBondInc]

Hello,

I have a variable text formula which builds a file path dependent on other portions of my spreadsheet.

Its formula resembles:

=HYPERLINK(CONCATENATE("file://",Manifest!H$1,DT!B$1,"/",H$1,"/",A$1,"-",AAI$2,"-",TEXT(A38,"dd"),"-",AAI$1,".xlsx","#",B$6,"!","J$4"))

which returns a file path resembling:

file://NETWORK/DEPT CODE/PRODUCTION/PRODUCTION FOLDERS/EMPLOYEE NAME/MM - MMMM/EMPLOYEE INITIALS-MM-DD-YY.xlsx#WORKSHEET NAME!J$4

I know that all of my hyperlinks work and advance to their specified location, and don't return an error.
___________________________________________________

What I need is to find a way to automate the process of referencing and returning the data from J$4 on the other end of the hyperlink, and seeing that data in the cell with the hyperlink (while keeping in mind I need to retain the hyperlink properties).

I've tried using several different methods to successfully achieve my desired result, but have obviously not succeeded.

Any ideas/comments what might help me out?

[davesexcel]

You may have to supply others something to work with.

Indirect can be used for hyperlinks.

[PatrickJ@GoldBondInc]

I actually figured it out. I used the reference that i wanted to return data, so pretty much the filename produced by the above mentioned formula, and set it as the Friendly_Name in the Hyperlink function.