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.

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

You haven't replied in regards to having the workbooks open. If you
don't care if they need to be open, try removing the path.
pikapika13 wrote:
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

The workbooks will be closed. Is this possible without VBA? and if it isn't
possible could someone send me in the right direction as to how to go about
doing this?

" wrote:

You haven't replied in regards to having the workbooks open. If you
don't care if they need to be open, try removing the path.
pikapika13 wrote:
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