Hi keanoppy...

Ok, I will try to explain the formulas.

The goal was to hyperlink to a specific cell on another sheet. If we do this manually, we have, for example, to use [PID.xls]PID_Status!$A$24 as first argument in the Hyperlink() function. This argument includes the filename, the target sheet name and the cell address.

So, what I have done in the sample workbook, is to retrieve this information dynamically by using Excel functions. In case you change the filename or the sheet name of the target sheet, you will not have to replace static texts in your formulas.

For retrieving the filename and sheet name, we can use the Excel Cell() function. This function returns some infos about cells and the workbook by using keywords. One of these keywords is "Filename". So, if we write in a cell the formula CELL("Filename";PID_Status!$A$1), then we will get E:\Temp\[PID.xls]PID_Status as result. However, this result also includes the path to the workbook. Please note, that CELL() can only return a value, if the workbook was already saved somewhere. By the way: the keywords are normally localized to the Excel language used. The english keywords always work, the localized only on Excel with the same language.

So, for getting [PID.xls]PID_Status from E:\Temp\[PID.xls]PID_Status, we need to split the string from "[". This can be done by using the functions Find(), Right() and Len(). Let's imagine, the result of Cell() is contained in the cell B1. Then we can use =RIGHT(B1;LEN(B1)-FIND("[";B1)+1). Find() searches the position of the "[" character and retuns a value. We then take the right part of the string in B1 from the found position of "[" and for including the "[", we increase the value by one. The result will be [PID.xls]PID_Status. The first part for the hyperlink is now found.

You said that the PID's are unique identifiers, this allows us to search the relative position of an PID in a matrix by using the Match() function. Example: =MATCH(A2;PID_Status!$A$5:$A$24;0) returns 20, meaning the 20th entry in PID_Status!$A$5:$A$24. As in my sample the PID States start in row 5, I added the relative position of the head row by using ROW(PID_Status!$A$4). I did the same for the columns by not entering the column number directly but using the formula COLUMN(PID_Status!$A$4).

Now we have, depending on the searched PID, found the cell row and column numbers in the target sheet. We need then to get the address of this cell, what can be done by using the Address() function from Excel. Examples: Adress(1;1) returns $A$1 and Adress(8;10) returns $J$8.

And after that, we just need to put the results together for building the needed hyperlink string: =HYPERLINK(The_string_got_by_splitting_the_fileinfo & "!" & The_address_found;"Friendly text"). The last step can optionally be putting the formulas together. Of course, the solution may not be the only one, perhaps there is a quicker solution.

Hope this helps

Regards