Forum Discussion
Dynamic Hyperlink: open a document (with the same value as in the cell) in a folder via a cell value
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
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.
- 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!
With your full hyperlink text in cell A1, use this formula:
=HYPERLINK("file:"&A1, "Link anchor text goes here")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 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:".
