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.