retrieve cell data from closed workbook

[GedSalter]

I am trying to retrieve a range to cell data from a closed workbook, but the workbook name is a variable in a cell referenced in a worksheet Analysis Cell B1.

The data is on a workbook whose File path is "C:\Users\61458\Desktop\TEST\Windows32\data" and worksheet called "Summary" and the cell data is a range of A1:A40.

I have tried the following but just returns an error.

=INDIRECT("'C:\Users\61458\Desktop\TEST\Windows32\data\[Analysis!&B1&]" Summary "'!$A$1:$A$40")

Hope you can help

 

[Fluff]

How about

=INDIRECT("'C:\Users\61458\Desktop\TEST\Windows32\data\["&Analysis!B1&"]Summary'!$A$1:$A$40")

 

[GedSalter]

How about

[QUOTE="Fluff, post: 5705252, member: 289073"]
How about[CODE=xls]
=INDIRECT("'C:\Users\61458\Desktop\TEST\Windows32\data\["&Analysis!B1&"]Summary'!$A$1:$A$40")

sorry fluff
[/QUOTE]

still returns #REF error

 

[Fluff]

Is the other workbook open?

 

[GedSalter]

Is the other workbook open?

CLOSED

 

[Fluff]

In that case it won't work. Indirect only works if the other workbook is open.

 

[GedSalter]

Is the other workbook open?

I have the 2 open but still not working.

 

[Fluff]

What is in B1 on the Analysis sheet?

 

[GedSalter]

thats a variable. its actually a stock number which will change

 

[GedSalter]

thats a variable. its actually a stock number which will change

the stock number becomes the file name. ie. TLS will reference to a file TLS,alsx