Strange HYPERLINK() behaviour...

Hi,

I am using the HYPERLINK() function to make clickable links to external PDFs. My hyperlinks are composed as follows:

=HYPERLINK("[filepath head]"&[CELL REF]&".pdf","Click here")

where the CELL REF is to an adjacent cell that contains the PDF's filename as a string (but without the suffix, which is why I am adding it in the formula).

The hyperlinks work - there is nothing wrong with the formula - but they don't work until I do something that seems unrelated first. A hyperlink constructed with the above formula will return a "Cannot open the specified file" alert, unless I create a direct hyperlink to the same pdf in another cell and open it once. Then my HYPERLINK() cell will open the file correctly, even though I have changed nothing in the formula?!?

I really don't understand why this is happening, and as my hyperlinks will be created dynamically, I can't go and create a direct link next to each one as a work around.

Any ideas why this would be occurring and how I might solve it would be greatly appreciated.

Thanks

Does the [CELL REF] contain the partial name of pdf?
I suggest to replace it with:
INDIRECT([CELL REF])

Hi bebo021999,

No, that doesn't work, I'm afraid. Same problem.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

That doesn't matter - I want to see exactly what you've done.

Just to clarify - experience with these things tells me that this will be something very small and peculiar to the way you have set up the worksheet, and probably not something we will be able to spot from your descriptions. Happy to have a look, but it's up to you.

I have attached a sample sheet. It has 2 columns of links. Column B has the links composed with "&", column C has the links composed using CONCATENATE(). Neither of those solutions is working first time. I have to manually open the link, as described first...

[This message deleted]

In column "A" Enter your file name suppose "A2"
In column "B" Enter Path suppose "B2"
In column "C" :

Formula:

Please Login or Register  to view this content.

Try this:

=HYPERLINK("Enclosing folder\"&A1&".pdf","LINK")

Change the forward slash to a backslash.

The relative paths are the problem - you need full filepaths similar to this:

=HYPERLINK("C:\Users\Alison\Documents\"&A1&".pdf","LINK")

Can you try with as per Ali say's "Change the forward slash to a backslash."

This discussion suggests that relative paths should work as long as they remain unchanged: https://www.ablebits.com/office-addi...link-function/

You have now mentioned DropBox.

It might be wirth reading the description of the workings of a relative file path here: https://www.excelfunctions.net/excel...-function.html

Does this explain your anomalies?

I think that best. Let us know what you find out.