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)