Hyperlinking from an array

Hello,

I'm in the process of making a calendar that looks up what reports my business does on a weekly basis and provides hyperlinks to the standard work for each report from an array. I've gotten the formulas to return which reports we do and the correct hyperlink shows up when I hover over the cell, but the cell isn't colored blue like regular hyperlinks and also when I click on it, it returns a message "Cannot open the specified file." The file does open when I open it from the array though. Let me know if you know what the problem is. Here is the formula.


{=IF(ISERROR(INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,1)),"",HYPERLINK(INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,2),INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,1)))}

I condensed the formula to only look at the hyperlink itself.

{=HYPERLINK(INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,2),INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,1)))}

Now the hyperlink it returns is blue like a regular hyperlink, but I'm still getting the same message when I try to open the hyperlink. "Cannot open the specified file." I'm starting to think that hyperlinks don't work well with the array command (Ctrl+shift+enter)

Hyperlink formula works with array.

What is the string returned from Index() part of your formula (i.e. without hyperlink)?

Does is have full path? (Ex. C:\Test\Test.xlsb) If not you need full path, not just file name.

Originally Posted by CK76

Hyperlink formula works with array.

What is the string returned from Index() part of your formula (i.e. without hyperlink)?

Does is have full path? (Ex. C:\Test\Test.xlsb) If not you need full path, not just file name.

{=IF(ISERROR(INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,1)),"",HYPERLINK(INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,2),INDEX(Calandar!$C$2:$I$45,SMALL(IF(Calandar!$E$6:$E$45="x",ROW(Calandar!$E$6:$E$45)),ROW(1:1))-1,1)))}

Not sure which index you're talking about so I will explain all three. The first index just checks to see if there isn't an "x" in that days column. If true, cell returns blank (""). If false, the second index function looks for the hyperlink from the array (in column 2 of array) Last index looks for name of the hyperlink in the array (in column 1 of array)

The one inside Hyperlink As that's what's used to build link.

Originally Posted by FDibbins

Did you read post #3?

Yes, I tried to implement that, but was unsuccessful. However I did just figure out what was wrong. Nothing was wrong with the formula itself, it was the file pathway that I screwed up on. *facepalm* I forgot to add .docx to the end of the pathway. The hyperlinks work now.