Dynamic Hyperlink: open a document (with the same value as in the cell) in a folder via a cell value

Copper Contributor

Hello,

 

I want to be able to open a document via a dynamic hyperlink.

 

In an Excel file I have 1 fixed cell that always contains a different article number after it has been selected from a list.

Then I want to open another Excel file via a link, which is located in a fixed folder.

This folder contains several Excel files.

Each Excel file has the same value as the value in the fixed cell can have. The name therefore corresponds to the article number followed by .xlsx.

 

Example:

in the fixed cell I select: 123456 (The length of the item number can be longer or shorter)

I then want to open the Excel file 123456.xlsx via the link I created.

The file to open is located in a Sharepoint folder.

 

Does anybody have experience with this?

 

If I am not clear enough, please let me know.

 

Thanks for the effort!

 

Kind regards, Nanno

11 Replies

@Nanno_S 

With your full hyperlink text in cell A1, use this formula:

=HYPERLINK("file:"&A1, "Link anchor text goes here")

 

@Jan Karel Pieterse 

Thanks for your response!

 

I can't get it working yet.

 

I have tried this.

=HYPERLINK(H9&N3;TEXT)

In cell H9 is the selected article number, which is selected via a drop down menu. Just text, no links attached.

In cell N3 states the full hyperlink tekst to the sharepoint folder, in which are all the individual article files.

TEXT is the text which I have stated in K6, on which one need to click to go to the specific file.

 

Is there something I'm doing wrong?

 

Thanks in advance!

@Nanno_S 

If I understand your goal correctly, you could explore setting a hyperlink base for the workbook.

Work with links in Excel - Microsoft Support

See: Set the base address for the links in a workbook

 

With a base set, you'd only need to refer to the file name in the fixed location.

@Nanno_S If the actual entire hyperlink ends with the article number, shouldn't the two cells be reversed in your formula?

=HYPERLINK("file:"&N3&H9;TEXT)

 

 Make sure the result of the first argument of that hyperlink function evaluates to a valid URL, preceded with "file:".

@Patrick2788
Thanks for your option!
But in my workbook I have multiple links to other folders.
So, if I understand this option correct (wet the base address for ALL the links in my workbook) then this does not work for me.

But thanks for your thoughts!
@Jan Karel Pieterse

Thanks for the addition!

I have it working but not completely.
The file is being downloaded instead of opened in the browser.

What I have now is this:
=HYPERLINK("file:"&N3&H9&N1;"TEXT")
where:
N3= the link to the folder on Sharepoint
H9= 123456 (article number)
N1= .xlsx
TEXT= the text which I have stated in K6, on which one need to click to go to the specific file.

Do I need to add an action to open the file in the browser?

Thanks again.
Hmm, not sure. What happens if you remove the "file:"& bit?

No, difference.
I have tried multiple options to see if something works.
I even took parts of the URL and add different kind of texts to it including the article number forcing the URL to open the file.
What I notice is that the URL to, only, the folder is different then the URL part to the folder in the complete file URL.
And the last part of the file URL is "&action=default&mobileredirect=true". So, I have added this to the URL in my link as well. But this does not work either.
What I want is a challenging one, I think.

Thanks for the link!
I will try this platform as well.

@Jan Karel Pieterse 

I have found the issue and the solution.

 

To open the download file automatically, you must adjust the setting in the browser.
Go to the downloaded file in the browser and right-click on the file and select the option 'Always open files of this type'. Repeat these steps for all types of documents you want to download/open. Most common types are Word, Excel, CSV and PDF files.

 

Thanks for your input!