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.

I have downloaded the function and when I use it, I am receiving #VALUE! for
each cell I use Indirect.ext in. I am not following the steps for the copy
and paste into another cell you described in your previous instructions:

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.

Is there an easier way to go about using this function?

"Harlan Grove" wrote:

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.

IntricateFool wrote...
I have downloaded the function and when I use it, I am receiving #VALUE! for
each cell I use Indirect.ext in. I am not following the steps for the copy
and paste into another cell you described in your previous instructions:
....
Is there an easier way to go about using this function?

No. Referring to cells in other workbooks is inherently one of the most
difficult tasks in Excel. The main task is ensuring that the external
reference is correct. If you want to refer to a range like
'C:\x\y\z\[state_XX.xls]some worksheet'!$A$5:$H$24 where the XX part of
the filename would vary, you need to ensure that constant external
references work. If XX were, e.g., then the formula

=COUNT('C:\x\y\z\[state_XX.xls]some worksheet'!$A$5:$H$24)

will either return a number when the workbook C:\x\y\z\state_XX.xls
exists and contains the particular worksheet named 'some worksheet', or
it'll return #REF! indicating that either the file doesn't exist or
there's no such worksheet in that file.

If the formula above returns a number, so should both

=COUNT(INDIRECT.EXT("'C:\x\y\z\[state_"&"XX&".xls]some
worksheet'!$A$5:$H$24"))

and

=COUNT(pull("'C:\x\y\z\[state_"&"XX&".xls]some worksheet'!$A$5:$H$24"))

provided you have installed *AND* loaded the MOREFUNC.XLL add-in or put
the code for pull into a general module in your workbook.

Your formula two follow-ups ago makes it appear you want to use
formulas like

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

The expression within this formula that produces the external reference
is

"'C:\Medicaid Data Collection\[Reimburse_Policy_Report_"&
A29&".xls]Please complete (Pharmacy)'!$C$6:$D$23"

You need to evaluate JUST THAT expression, so if A29 were "WA" (without
the quotes), this expression should evaluate to

'C:\Medicaid Data Collection\[Reimburse_Policy_Report_WA.xls]Please
complete (Pharmacy)'!$C$6:$D$23

*and* this *constant* external reference when used as the argument to
COUNT should return a number, i.e.,

=COUNT('C:\Medicaid Data
Collection\[Reimburse_Policy_Report_WA.xls]Please complete
(Pharmacy)'!$C$6:$D$23)

should return a number. If it does, then

=COUNT(
INDIRECT.EXT("'C:\Medicaid Data
Collection\[Reimburse_Policy_Report_WA.xls]Please complete
(Pharmacy)'!$C$6:$D$23"))

should return the same number. If it doesn't, then INDIRECT.EXT doesn't
work on your PC, so you'll have to use pull. If the formula immediately
above does return a number but

=COUNT(
INDIRECT.EXT("'C:\Medicaid Data Collection\[Reimburse_Policy_Report_"&
A29&".xls]Please complete (Pharmacy)'!$C$6:$D$23"))

returns an error, then the problem almost certainly lies in A29 rather
than this formula. Check for leading or trailing spaces in A29. You
can't have stray spaces when constructing external references.
Drive/directory path, filename, worksheet name and range address must
all be exactly correct for external references to work.

I finally got it to work.... The problem is when I drag the function to other
cells it crashes Excel. I guess this was my only way of doing this besides
throwing together some VBA code?

All of your help has been greatly appreciated.



"Harlan Grove" wrote:

IntricateFool wrote...
I have downloaded the function and when I use it, I am receiving #VALUE! for
each cell I use Indirect.ext in. I am not following the steps for the copy
and paste into another cell you described in your previous instructions:
....
Is there an easier way to go about using this function?

No. Referring to cells in other workbooks is inherently one of the most
difficult tasks in Excel. The main task is ensuring that the external
reference is correct. If you want to refer to a range like
'C:\x\y\z\[state_XX.xls]some worksheet'!$A$5:$H$24 where the XX part of
the filename would vary, you need to ensure that constant external
references work. If XX were, e.g., then the formula

=COUNT('C:\x\y\z\[state_XX.xls]some worksheet'!$A$5:$H$24)

will either return a number when the workbook C:\x\y\z\state_XX.xls
exists and contains the particular worksheet named 'some worksheet', or
it'll return #REF! indicating that either the file doesn't exist or
there's no such worksheet in that file.

If the formula above returns a number, so should both

=COUNT(INDIRECT.EXT("'C:\x\y\z\[state_"&"XX&".xls]some
worksheet'!$A$5:$H$24"))

and

=COUNT(pull("'C:\x\y\z\[state_"&"XX&".xls]some worksheet'!$A$5:$H$24"))

provided you have installed *AND* loaded the MOREFUNC.XLL add-in or put
the code for pull into a general module in your workbook.

Your formula two follow-ups ago makes it appear you want to use
formulas like

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

The expression within this formula that produces the external reference
is

"'C:\Medicaid Data Collection\[Reimburse_Policy_Report_"&
A29&".xls]Please complete (Pharmacy)'!$C$6:$D$23"

You need to evaluate JUST THAT expression, so if A29 were "WA" (without
the quotes), this expression should evaluate to

'C:\Medicaid Data Collection\[Reimburse_Policy_Report_WA.xls]Please
complete (Pharmacy)'!$C$6:$D$23

*and* this *constant* external reference when used as the argument to
COUNT should return a number, i.e.,

=COUNT('C:\Medicaid Data
Collection\[Reimburse_Policy_Report_WA.xls]Please complete
(Pharmacy)'!$C$6:$D$23)

should return a number. If it does, then

=COUNT(
INDIRECT.EXT("'C:\Medicaid Data
Collection\[Reimburse_Policy_Report_WA.xls]Please complete
(Pharmacy)'!$C$6:$D$23"))

should return the same number. If it doesn't, then INDIRECT.EXT doesn't
work on your PC, so you'll have to use pull. If the formula immediately
above does return a number but

=COUNT(
INDIRECT.EXT("'C:\Medicaid Data Collection\[Reimburse_Policy_Report_"&
A29&".xls]Please complete (Pharmacy)'!$C$6:$D$23"))

returns an error, then the problem almost certainly lies in A29 rather
than this formula. Check for leading or trailing spaces in A29. You
can't have stray spaces when constructing external references.
Drive/directory path, filename, worksheet name and range address must
all be exactly correct for external references to work.