Forum Discussion
File name, referenced in a hyperlink, located in a different cell
I would like to create a fixed hyperlink in cell I5 in a worksheet named ABC.
I would like this hyperlink to reference cell B5 in a worksheet within a separate directory. This file is named 20210830ABC.
The file name for file 20210830ABC will change daily, as it is based on YEAR|MONTH|DAY. Tomorrow, it will be named 20210831ABC. I do not want to have to update the hyperlinks on worksheet ABC every day.
Within worksheet ABC, I would like to have cell J11 indicate the name of the file that the hyperlink should reference. As file 20210830ABC is subject to change to 20210831ABC, the link in cell I5 will not work unless the file name within this cell is updated.
So instead of cell I5 on worksheet ABC referencing \\server\directory\filename\B5...
It would reference \\server\directory\"CELL J11"\cell B5.
If this is possible, how do I edit the hyperlinks so they use the text in a cell as part of the location of the linked file?
- Thank you. I added a \ to the end of the file path in your example and that worked. Great support! I appreciate the help very much.
7 Replies
=HYPERLINK(ADDRESS&FILENAME)
=HYPERLINK("\\server\directory\"&J11)To retrieve the value inside the file it needs to be opened to work using the INDIRECT formula
=INDIRECT("'"&ADDRESS&"["&FILENAME&"]Sheet1'!$B$5")
=HYPERLINK("\\server\directory\"&J11,INDIRECT("'"&"\\server\directory\"&"["&J1&"]Sheet1'!$B$5"))
The INDIRECT formula works but the file must be opened.
- I appreciate the response but I am unclear. Do I paste this string into cell I5?
=HYPERLINK("\\server\directory\"&J11,INDIRECT("'"&"\\server\directory\"&"["&J1&"]Sheet1'!$B$5"))
If so, do I remove any of the quotes? It isn't working. Thanks.
