VLOOKUP & INDIRECT

[Graham Y]

I read some other posts but I still can't get this to work...
in cell A1 is fred
in cell B1 is =VLOOKUP(A1,'C:\Documents and Settings\gy\My Documents\one
1.xls'!peeps,2)
where peeps is a named range - this WORKS FINE!
But what I really want to do is have the 'C:\Documents and Settings\gy\My
Documents\one 1.xls'!peeps in a cell, because I want to be able to change the
path using VBA (this is a test sheet, I need to look at last months data, and
the folders are of the format yymm mmm)

My expectation was that I shuld be able to have in cell C1
'C:\Documents and Settings\graham.yetton\My Documents\one 1.xls'!peeps
and in C2 =VLOOKUP(A1,INDIRECT(C1),2)
But it doesn't work

Can anyone tell ne why?
Please

 

[Dave F]

INDIRECT and a number of other functions do not work if they reference
closed, external workbooks. If you really need to use INDIRECT either plan
to have the external workbook opened or else have the data referenced by
INDIRECT on another worksheet within the same workbook.

Dave

 

[Graham Y]

Dave Thanks
The problem relates to using the same file name in different folders, which,
of course, XL won't let me open at the same time.
Looks like it'll be more VBA to grab the data.