Lookup more than one workbook

[IntricateFool]

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]

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.

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.

[[email protected]]

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

[IntricateFool]

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?

"[email protected]" 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
>
>