SOLVED

File name, referenced in a hyperlink, located in a different cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2699987%22%20slang%3D%22en-US%22%3EFile%20name%2C%20referenced%20in%20a%20hyperlink%2C%20located%20in%20a%20different%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2699987%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20create%20a%20fixed%20hyperlink%20in%20cell%20I5%20in%20a%20worksheet%20named%20ABC.%3C%2FP%3E%3CP%3EI%20would%20like%20this%20hyperlink%20to%20reference%20cell%26nbsp%3BB5%20in%20a%20worksheet%20within%20a%20separate%20directory.%20This%20file%20is%20named%2020210830ABC.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20name%20for%20file%26nbsp%3B20210830ABC%20will%20change%20daily%2C%20as%20it%20is%20based%20on%20YEAR%7CMONTH%7CDAY.%20Tomorrow%2C%20it%20will%20be%20named%2020210831ABC.%20I%20do%20not%20want%20to%20have%20to%20update%20the%20hyperlinks%20on%20worksheet%20ABC%20every%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithin%20worksheet%20ABC%2C%20I%20would%20like%20to%20have%20cell%20J11%20indicate%20the%20name%20of%20the%20file%20that%20the%20hyperlink%20should%20reference.%20As%20file%2020210830ABC%20is%20subject%20to%20change%20to%2020210831ABC%2C%20the%20link%20in%20cell%26nbsp%3BI5%20will%20not%20work%20unless%20the%20file%20name%20within%20this%20cell%20is%20updated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20instead%20of%20cell%20I5%20on%20worksheet%20ABC%20referencing%26nbsp%3B%5C%5Cserver%5Cdirectory%5Cfilename%5CB5...%3C%2FP%3E%3CP%3EIt%20would%20reference%26nbsp%3B%5C%5Cserver%5Cdirectory%5C%22CELL%20J11%22%5Ccell%20B5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20is%20possible%2C%20how%20do%20I%20edit%20the%20hyperlinks%20so%20they%20use%20the%20text%20in%20a%20cell%20as%20part%20of%20the%20location%20of%20the%20linked%20file%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2699987%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2702884%22%20slang%3D%22en-US%22%3ERe%3A%20File%20name%2C%20referenced%20in%20a%20hyperlink%2C%20located%20in%20a%20different%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702884%22%20slang%3D%22en-US%22%3EI%20appreciate%20the%20response%20but%20I%20am%20unclear.%20Do%20I%20paste%20this%20string%20into%20cell%20I5%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3DHYPERLINK(%22%5C%5Cserver%5Cdirectory%5C%22%26amp%3BJ11%2CINDIRECT(%22'%22%26amp%3B%22%5C%5Cserver%5Cdirectory%5C%22%26amp%3B%22%5B%22%26amp%3BJ1%26amp%3B%22%5DSheet1'!%24B%245%22))%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20so%2C%20do%20I%20remove%20any%20of%20the%20quotes%3F%20It%20isn't%20working.%20Thanks.%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

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.

@Danger_SF 

 

Please give feedbacks if the solution meets your needs

best response confirmed by Danger_SF (Occasional Contributor)
Solution
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.
Any timemy friend. Please just hit the Like Button and flag it as solved.
Ok, now this is odd. Maybe you can help.

2 days ago, I created these hyperlinks on file #1. Yesterday in the morning I opened file #1 and file #2 and the hyperlink directing to file #2 was broken. The same afternoon I opened the two files and the hyperlink worked. ???

What outside influences could affect file #1's ability to access data in file #2? Both files are located within the same parent directory on a shared drive. It wouldn't be a security policy since it worked twice and failed to work twice. Maybe add-ons?
Update:

As we all know, hyperlinks to shared files only work when both files are open. My problem occurs when I open both files via my Windows Explorer shortcut on my PC.

When I use Excel (File #1) to access the directory to open the linked file (File #2), the hyperlink works. What I mean by that is that I have the file path for the linked directory (which houses File #2) written out in the worksheet of File #1. When I click on that text, Excel causes Windows Explorer to open. When I open the file from that window, the hyperlink works.

I don't understand why that works, but if that helps anyone else to understand a setting in Excel or in Windows that is keeping the hyperlink from working otherwise, I'd be interested in understanding.