Adding a hyperlink from a worksheet in one Excel workbook to a specific sheet/cell of a worksheet in another workbook .... 

how hard can that be?

Easy right?

Sure ... every forum says so ...
Just add the formula
=hyperlink("c:\path name\spreadsheet.xlsx#SheetName!$A$4","Text to display in cell to click")
You can even use UNC paths  
=hyperlink("\\server name\share name\path name\spreadsheet.xlsm#Sheet!B10","Click Me")

Sooo easy ... except when you consistently get  "Cannot open the specified file" each time you click the link

Why?
Make sure you have removed any previously applied hyperlink BEFORE creating your hyperlink formula!
Select the cell with the formula in it, right-click and select "Remove Hyperlink"
NOW (re)create your correctly formed hyperlink formula in that cell (as per all the many forum examples) and BEHOLD! it will probably work now!

Summary
If for some reason there is a failed pre-existing hyperlink attached to the cell, it must be completely removed before applying the cell-formula based hyperlink.