Thank you for your reply. I have read through the linked resources, and unfortunately they are not addressing exactly the same problem. I am having trouble with the links on my own machine (haven't even gotten to the problems with other users trying to access them yet!) I am trying to link resources in my local file structure. All resources are in the same folder (true for my colleagues as well) so relative paths should work fine.
Now, I can hard-code hyperlinks from my excel sheet to the resources, which work. So the resources are accessible - it's not a permissions issue. When I build links dynamically using the HYPERLINK() formula, however (even though I know how to use this formula and the paths are correct), I get the "Cannot open this resource" error. It doesn't matter if I build the links as relative or absolute - same problem. I have also tried using back and forward slashes (I am working on a Mac) and that makes no difference.
BUT (this is the weird bit), these same links will suddenly start working mysteriously if I create a hard link to the same resource in another cell, and use it once (to open the target doc). After that, the link constructed with HYPERLINK() will work... even though I have not altered it in any way. But I would have to do that individually for each linked resource - i.e. open it manually - before my formula-constructed links would work (which reders the whole exercise pointless). I have never seen behaviour like this and am quite perplexed as to what is going on.
A colleauge suggested linking directly to dropbox online, rather than to the local file structure. A constructed link using HYPERLINK() straight to https://dropbox does indeed work, but that's where I struck the issue with the 15 character codes in the filepath. It looks as if DropBox stores each file in its own directory with a randomly generated codename that I cannot reconstruct in my Excel formulas, meaning I cannot dynamically create links to these resources, which is the whole point...
(The dl=0/dl=1 parameter is not a problem. The linked files are pdfs so they render fine in a browser.)