Defining a name

[TwEaKFrEaK]

Whats up everyone, I need some of your expertise.

I want to define a name call it Data1 and have it equal sheet 1 Colum
b row 15.

On sheet 1 column b row 15 I have entered a formule like this
='S:\Local disk\Excel files\[Test.xls]Performance'!$A$2:$J$402


Can I do this?

For some reason I can not get
=VLOOKUP(Reference!$E$10,Data1,Reference!F10,FALSE) to display th
proper data.

If I manually define the name as ='S:\Local disk\Exce
files\[Test.xls]Performance'!$A$2:$J$402 it works perfect. The issu
is the file name will change every quarter so I created an input shee
to capture the file name and concat it.
Any help would be great. I know what I wrote probably is ver
confusing if you have any questions let me know

 

[Frank Kabel]

Hi
try:
=VLOOKUP(Reference!$E$10,INDIRECT(Data1),Reference!F10,FALSE)

BUT this will work only if the other file is OPEN. It won't work for
closed files