Excel hyperlink not working (tried all of the common fixes)

[mlewis715]

OK, so I consider myself to be an advanced level Excel user, but I'm having a problem with a hyperlink formula that I can't figure out. After a week of fighting it and striking out finding a solution through extensive online research, I am officially raising the white flag and requesting some expert guidance.

I have a locally saved spreadsheet that I'm trying to link up to another spreadsheet saved on a network drive via the following formula:

=HYPERLINK(INDEX('\\hgtfs01\Common\TDL''s\[0255 MDL.xlsx]0255xxxx'!$A:$A,MATCH(1,(F3='\\hgtfs01\Common\TDL''s\[0255 MDL.xlsx]0255xxxx'!$C:$C)*(G3='\\hgtfs01\Common\TDL''s\[0255 MDL.xlsx]0255xxxx'!$T:$T),0)))

So, I'm asking Excel to search the network spreadsheet for a row that meets BOTH of the following criteria:

1) Value in column C of the network sheet matches the value in cell F3 of the locally saved sheet
AND
2) Value in column T of the network sheet matches the value in cell G3 of the locally saved sheet

Once the row is located on the network sheet that meets both of those criteria, I want Excel to populate the value from column A of the network sheet into the local sheet as a hyperlink that, when clicked on, will open the network sheet and point the user to the matching cell in column A.

I'm also using CTRL-SHIFT-Enter once the formula is entered as I'm told that is required to 'activate' the hyperlink (although I'm not sure I understand why that's necessary or how it's different than just pressing Enter!)

Here's the problem: the formula seems to be working (locates the matching row from the network sheet and populates the value from column A into the local sheet), but the hyperlink doesn't work (doesn't open the network sheet).

Additionally, I noticed that if I have both files open and click on the link in the local sheet, an "Cannot open the specified file" error box pops up. But, if I close the network sheet and click on the link again, no error box pops up. Either way, the hyperlink fails to open the target file or to navigate to the matching cell.

So, here are my questions:

1) The MATCH(1,(condition1)*(condition2),0) is a new one for me. I found it online as a way to use multiple criteria for the INDEX(MATCH... function. So, am I properly integrating it into the INDEX(MATCH... function?
2) Are there any other structural errors in my formula?
3) Do I need to do anything differently in my formula to allow the hyperlink to work with a network-saved file?

As stated above, I've already done extensive online research for a solution, so to avoid wasted responses about common/obvious fixes, I've already addressed/confirmed the following:

- Local sheet is not protected
- I've already established the network folder that the target spreadsheet is saved in as a "trusted location" through the trust center settings in the options menu
- Cells in the local sheet where the hyperlink formula is entered are formatted as "General" format

One other note: The actual path to the network file is:

\\hgtfs01\Common\TDL's\[0255 MDL.xlsx]0255xxxx

But, when I enter the formula, Excel changes the TDL's to TDL''s. I'm assuming that's because the apostrophe would cause problems for the formula. So, after I enter the formula, Excel changes the path to:

'\\hgtfs01\Common\TDL''s\[0255 MDL.xlsx]0255xxxx'

I'm assuming that the combination of changing TDL's to TDL''s and the addition of the apostrophes at the start and end of the path are how Excel works around the monkey wrench of having an apostrophe in the formula.

Lastly, the network target file is a read only file. I was wondering if that might be a problem, but in the course of researching solutions online, I couldn't find anything about read-only files causing problems for hyperlink functions, but I wanted to at least point that out in case the group here has a different opinion.

Oh yeah, 1 other thing, if I right click on the cell in my local spreadsheet that has the hyperlink formula entered, the option to "Edit Hyperlink" is greyed out. The option to "Remove Hyperlink" is there, but I can't edit it. Strange.

Thanks!