INDIRECT will work for you, but the workbooks must be open. Please post
what you've tried with INDIRECT. Here's a format that I use(the most
tricky part is where to put quotes and the & signs:
=VLOOKUP(A10,INDIRECT("[Soltime"&A1&".xls]"&"Sheet1!$B$5:$H$11"),5,FALSE).
If you don't want the workbooks to be open but to be linked, you may
need some VBA.


IntricateFool Wrote:
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.


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=548600