INDIRECT Function - what am I doing wrong?
 

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

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" wrote in
message ...

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

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


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile: http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=471871