I am trying to lookup multiple workbooks in a hlookup function. Each file
name changes only by the State Abbreviation it contains data for. For example:

Reimburse_Policy_Report_CO.xls
Reimburse_Policy_Report_MN.xls

I need to pull data from each of these 50 workbooks into one worksheet using
hlookup. Column A of this dump workbook contains the abbreviation of each
state. So each row would contain the data from each State Workbook. Currently
my function looks like:

=HLOOKUP($A6,("'C:\!Alison\Data
Collection\[Reimburse_Policy_Report_"&A6&".xls]Please complete
(Pharmacy)'!$C$6:$D$23"),C$1,FALSE)

I assumed I could just add "$A6&" to reference each state accordingly? Is
there a way to go about doing this? I have tried indirect.ext without much
luck. Please help.