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

What error are you getting?


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=547942

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