Updating a link in a file extension dynamically based on a field in that spreadsheet

Welcome to the Forum tmw2912! And howdy, neighbor!

The problem with what you need to do is that when you build an external reference with a formula, you need to use the INDIRECT function. And the INDIRECT function requires external files to be open, or the reference gives an error. For example, you want this:

=INDIRECT("'W:\Client\C\Client name\2013\Test File\Test Name\" & B2 & "\List\[filename.xls]test'!$H$60")

but it won't work unless the file is open.

The only option for an external reference to a closed file is macros (VBA). There are two ways to do it. The first is to use a VBA function instead of INDIRECT. There is an add-in for this called INDIRECT.EXT floating around the web, and also other similar solutions. The second way is to use VBA to update the formula in G7 whenever the content of B2 changes.

I happen to like the second one a little better for your particular case, because it's a little simpler to implement but if your formula changes the code has to be updated. Let me know if you want to go down this road. If you attach your file I can install the code for you.

You said "one value"--do you really just have one formula like this, or more?