Aug 30 2021 12:23 PM - edited Aug 30 2021 02:35 PM
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?
Aug 31 2021 05:44 AM - edited Aug 31 2021 05:52 AM
=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.
Aug 31 2021 06:40 AM
Aug 31 2021 07:02 AM - edited Aug 31 2021 07:55 AM
Aug 31 2021 08:59 AM
SolutionAug 31 2021 09:15 AM
Sep 02 2021 10:31 AM
Sep 02 2021 11:32 AM
Aug 31 2021 08:59 AM
Solution