Lookup multiple workbooks

[intricatefool]

I need to lookup cells in multiple workbooks. Each workbook has a state
abreviation at the end of the file name.

I am trying to use:

=HLOOKUP($A29,INDIRECT("C:\Data\Data
Collection\Reimburse_Policy_Report_"&A29&".xls]Pharmacy'!$C$6:$D$23"),C$1,FALSE)

A29 in the formula is the state abbreviation to be looked up in each file.
Anyone have any suggestions on how to go about doing this the correct way?

I am getting an error every time...! Please help!

[bgeier]

What error are you getting?

[DerekB]

If you are getting a #REF! errror, it may just be that you have not opened
the file(s) referred to in the formula. INDIRECT requires that the files be
open.

This requirement, in effect, means that you only really need to put the file
name into the function although the full path could be needed if there is the
chance having more than one file of the same name in different folders.

I am assuming, by the way, that the formula as given in your mail has not
been exactly reproduced. Otherwise, teh problem is that you have missed out
the opening single quite mark before the drive name C: and the opening square
bracket before the file name:

INDIRECT("'C:\Data\Data
Collection\[Reimburse_Policy_Report_"&A29&".xls]Pharmacy'!$C$6:$D$23"

--
DerekB


"intricatefool" wrote:

>
> I need to lookup cells in multiple workbooks. Each workbook has a state
>
> abreviation at the end of the file name.
>
> I am trying to use:
>
> =HLOOKUP($A29,INDIRECT("C:\Data\Data
> Collection\Reimburse_Policy_Report_"&A29&".xls]Pharmacy'!$C$6:$D$23"),C$1,FALSE)
>
> A29 in the formula is the state abbreviation to be looked up in each
> file.
> Anyone have any suggestions on how to go about doing this the correct
> way?
>
> I am getting an error every time...! Please help!
>
>
> --
> intricatefool
> ------------------------------------------------------------------------
> intricatefool's Profile: http://www.excelforum.com/member.php...o&userid=32919
> View this thread: http://www.excelforum.com/showthread...hreadid=547942
>
>