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 differently or
just a right way of doing this?

I am getting an error every time...! Please help!

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)
....

INDIRECT only works with open workbooks. If they're open, you don't
need to drive-directory path. However, you're missing a left square
bracket after the rightmost backslash.

If these other workbooks are closed, then your options are given in the
following archived article.

http://groups.google.com/group/micro...443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

The link you provided is not very clear for me. I am trying to use what is
given with the "Pull" but I am not getting a result. Could you use my
original formula to show me what I am doing wrong? The ones located on the
link you provided are very hard to follow.

I really appreciate your help with this. Thanks

"Harlan Grove" wrote:

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)
....

INDIRECT only works with open workbooks. If they're open, you don't
need to drive-directory path. However, you're missing a left square
bracket after the rightmost backslash.

If these other workbooks are closed, then your options are given in the
following archived article.

http://groups.google.com/group/micro...443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

IntricateFool wrote...
The link you provided is not very clear for me. I am trying to use what is
given with the "Pull" but I am not getting a result. Could you use my
original formula to show me what I am doing wrong? The ones located on the
link you provided are very hard to follow.
....

Actually, you'd be better off using MOREFUNC.XLL for this. With it, try

=HLOOKUP($A29,
INDIRECT.EXT("'C:\Data\Data Collection\[Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"),C$1,0)

Formulas using pull would look similar.

=HLOOKUP($A29,
pull("'C:\Data\Data Collection\[Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"),C$1,0)

I didn't catch the missing single quote before the drive letter in my
previous response. When you have problems using either INDIRECT.EXT or
pull, copy the exact formula ([F2], select the entire formula,
[Ctrl]+C) and paste it into another cell, delete everything except the
argument to INDIRECT.EXT or pull and add a leading =, so in your case
you'd have been left with

="="&"C:\Data\Data Collection\Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"

which would have evaluated to something like

=C:\Data\Data
Collection\Reimburse_Policy_Report_XX.xls]Pharmacy'!$C$6:$D$23

Then copy that, paste it special as value in a different cell, then
[F2], [Enter], which would reenter it in that latest cell as a formula.
Valid external references to multiple cell ranges will return #VALUE!
errors, but invalid external references will return #REF!. If you get
#REF!, both INDIRECT.EXT and pull will also return errors.

For the reference above, it should have looked like

='C:\Data\Data
Collection\[Reimburse_Policy_Report_XX.xls]Pharmacy'!$C$6:$D$23

that is, a single quote as first character of the reference, and a left
square bracket just after the last backslash. The devil IS in the
details in external references.

Again i appreciate your help... I am still having trouble following.
Currently my formular looks like:

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

Where am I going wrong? Do I need to install MOREFUNC.XLL? Am I leaving
something out?

I didn't think it would be this hard...

"Harlan Grove" wrote:

IntricateFool wrote...
The link you provided is not very clear for me. I am trying to use what is
given with the "Pull" but I am not getting a result. Could you use my
original formula to show me what I am doing wrong? The ones located on the
link you provided are very hard to follow.
....

Actually, you'd be better off using MOREFUNC.XLL for this. With it, try

=HLOOKUP($A29,
INDIRECT.EXT("'C:\Data\Data Collection\[Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"),C$1,0)

Formulas using pull would look similar.

=HLOOKUP($A29,
pull("'C:\Data\Data Collection\[Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"),C$1,0)

I didn't catch the missing single quote before the drive letter in my
previous response. When you have problems using either INDIRECT.EXT or
pull, copy the exact formula ([F2], select the entire formula,
[Ctrl]+C) and paste it into another cell, delete everything except the
argument to INDIRECT.EXT or pull and add a leading =, so in your case
you'd have been left with

="="&"C:\Data\Data Collection\Reimburse_Policy_Report_"&A29
&".xls]Pharmacy'!$C$6:$D$23"

which would have evaluated to something like

=C:\Data\Data
Collection\Reimburse_Policy_Report_XX.xls]Pharmacy'!$C$6:$D$23

Then copy that, paste it special as value in a different cell, then
[F2], [Enter], which would reenter it in that latest cell as a formula.
Valid external references to multiple cell ranges will return #VALUE!
errors, but invalid external references will return #REF!. If you get
#REF!, both INDIRECT.EXT and pull will also return errors.

For the reference above, it should have looked like

='C:\Data\Data
Collection\[Reimburse_Policy_Report_XX.xls]Pharmacy'!$C$6:$D$23

that is, a single quote as first character of the reference, and a left
square bracket just after the last backslash. The devil IS in the
details in external references.

IntricateFool wrote...
Again i appreciate your help... I am still having trouble following.
....

Always be specific about what kind of trouble you're having.

Where am I going wrong? Do I need to install MOREFUNC.XLL? Am I leaving
something out?
....

Yes, you need to install MOREFUNC.XLL in order to use INDIRECT.EXT.