INDIRECT Function - what am I doing wrong?

[MACRE0]

I have a series of excel files 1 though 150. I would like to find i
column C (of the Cover Letter tab) the cell with GRAND TOTAL writen i
it and capture the figure to its right for each file.

This works:

=VLOOKUP("Grand Total",'D:\Documents and Settings\M
Documents\[1.xls]Cover Letter'!$C:$D,2,0)

I would like to drag down the list though and set up the numbers
through 150 in column A and the respective Grand Totals in B.
thought the INDIRECT Functions would allow me to do this.

=VLOOKUP("Grand Total",INDIRECT("'D:\Documents and Settings\M
Documents\["&A1&".xls]Cover Letter'!$C:$D"),2,0)
I also failed with:
=VLOOKUP("Grand Total",INDIRECT('D:\Documents and Settings\M
Documents\[&"A1"&.xls]Cover Letter'!$C:$D),2,0)


Where have I gone astray? Is there a better way for me to do what
need?

Thanks in advance for any assistance. :~

 

[RagDyer]

Do you realize that if you use the Indirect() function, that each WB *must
be open* in order for you to retrieve your data?

Harlan has created something that might be good enough for you, where it
"pulls" data from closed WBs.

Check out this link:

ftp://members.aol.com/hrlngrv/pull.zip


--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[MACRE0]

I did not realize, no :~( . But thanks and I'll review that lin