INDIRECT Function - what am I doing wrong?

[MACRE0]

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

This works:

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

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

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


Where have I gone astray? Is there a better way for me to do what I 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" <[email protected]> wrote in
message news:[email protected]...
>
> I have a series of excel files 1 though 150. I would like to find in
> column C (of the Cover Letter tab) the cell with GRAND TOTAL writen in
> it and capture the figure to its right for each file.
>
> This works:
>
> =VLOOKUP("Grand Total",'D:\Documents and Settings\My
> Documents\[1.xls]Cover Letter'!$C:$D,2,0)
>
> I would like to drag down the list though and set up the numbers 1
> through 150 in column A and the respective Grand Totals in B. I
> thought the INDIRECT Functions would allow me to do this.
>
> =VLOOKUP("Grand Total",INDIRECT("'D:\Documents and Settings\My
> Documents\["&A1&".xls]Cover Letter'!$C:$D"),2,0)
> I also failed with:
> =VLOOKUP("Grand Total",INDIRECT('D:\Documents and Settings\My
> Documents\[&"A1"&.xls]Cover Letter'!$C:$D),2,0)
>
>
> Where have I gone astray? Is there a better way for me to do what I
> need?
>
> Thanks in advance for any assistance. :~)
>
>
> --
> MACRE0
> ------------------------------------------------------------------------
> MACRE0's Profile:
http://www.excelforum.com/member.php...o&userid=10848
> View this thread: http://www.excelforum.com/showthread...hreadid=471871
>

[MACRE0]

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