Use a cell's value to set the source file name in link
 

I am trying to create a formula that will link to another file, where the
name of the file (or part of the name) is provided in another cell.

For example, instead of the formula:

='[File Name.xls]Sheet Name'!$C$5

I want to be able to enter:

='[D8]Sheet Name'!$C$5

Where cell D8 in the local worksheet contains the file name. The above
syntax does not work, and I've tried a number of other logical guesses, but
none of them work.

Thanks!

try

=INDIRECT("'"&D8&"Sheet Name'!$C$5")


"Lost in Parsippany" wrote:

I am trying to create a formula that will link to another file, where the
name of the file (or part of the name) is provided in another cell.

For example, instead of the formula:

='[File Name.xls]Sheet Name'!$C$5

I want to be able to enter:

='[D8]Sheet Name'!$C$5

Where cell D8 in the local worksheet contains the file name. The above
syntax does not work, and I've tried a number of other logical guesses, but
none of them work.

Thanks!

Sorry. Make that

=INDIRECT("'["&D8&"]Sheet Name'!$C$5")


"Lost in Parsippany" wrote:

I am trying to create a formula that will link to another file, where the
name of the file (or part of the name) is provided in another cell.

For example, instead of the formula:

='[File Name.xls]Sheet Name'!$C$5

I want to be able to enter:

='[D8]Sheet Name'!$C$5

Where cell D8 in the local worksheet contains the file name. The above
syntax does not work, and I've tried a number of other logical guesses, but
none of them work.

Thanks!