On Jul 13, 1:04 pm, bj wrote:
check out indirect()
=indirect("'C:\["&B1&"]Finance'!$E9+...
....

You forgot the closing double quote and the right parenthesis. But
more fundamentally, INDIRECT doesn't work unless the other files are
open. Excel can't load multiple files with the same base filename, so
OPEN files NEVER need their drive/directory paths preceding their base
filenames.

If the OP needs the drive/directory paths, then the OP's files aren't
open when being accessed, and in that case INDIRECT won't work.

For alternative workarounds, see

http://groups.google.com/group/micro...443753560f0075