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.