Would you care to tell me, the Help file of which version of XL states that
Offset is capable of calculating on closed WBs.
AFAIK, it's always been among the functions that *do not* perform on
*closed* WBs, similar to Sumif, Countif, and Indirect, among others.

Omitting the long path of the OP formula:

=OFFSET(B3,MATCH(A1,A3:A46,0)-1,MATCH(V6,B1:M1,0)-1)

Which will *not* link to closed WBs, can be replaced with this formula to
return the same results:

=INDEX(B3:M46,MATCH(A1,A3:A46,0),MATCH(V6,B1:M1,0) )

Which *does* link to closed WBs.


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ronald Dodge" wrote in message
.. .
Even though the help file states that it should be able to calculate from
closed workbooks, I have not found this to be a true statement. As a
matter
of fact, when calculations takes place, #Ref! error messages comes up.
Given I have several workbooks including one workbook per machine center
that's about 12MB each, I ended up having to turn to VBA to control the
opening and closing of workbooks and the flow of data for these various
reasons:

Reduce the amount of time it takes to calculate. The more calculations
that
are open, the longer it takes for Excel to calculate unless you use
something like Sheet level calculations.

Reduce the amount of RAM it takes (this was a definite issue and still is
as
XL97/2000 only allowed up to about 80MB of RAM usage, even if you have 1GB
of RAM on your system. XL2002/3 allows up to 160MB or so before
crashing.)

Provide greater control of the data and prevent Excel from crashing as
easily. Of course, this also meant that I had to knock down the number of
undo's from the default 16 to 4 via the registry due to the huge copy and
pasting that the program was doing before.

The real issue in this case is the fact it deals with external formula
links, which is crossing over multiple workbooks. There is nothing wrong
in
that itself, but you do have to keep in mind of the limitations of doing
that.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"DS" wrote in message
...
I have a match formula that shows correct data from a second exel file
but
when I close the second excel file the file with this formula replaces
the
correct data to show #value. How can I chg the formula to not require
the
second file to be one inorder to have the correct data?


OFFSET('U:\Department\General Accounting\Budget\2007\Data Files\[2007
Depreciation.xls]Software Amort'!$B$3,MATCH($A$1,'U:\Department\General
Accounting\Budget\2007\Data Files\[2007 Depreciation.xls]Software
Amort'!$A$3:$A$46,FALSE)-1,MATCH(V$6,'U:\Department\General
Accounting\Budget\2007\Data Files\[2007 Depreciation.xls]Software
Amort'!$B$1:$M$1,FALSE)-1)