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)

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)

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)

If you go to the contents of the Excel help file, which I have seen it in
XL97 as well as XL2K, which here's the path to it in XLXP:

Microsoft Excel Help
Creating and Correcting Formulas
Creating Links
Control when links are updated

Within there, you see under the sub content of "Links to other workbooks" as
implying that it can calculate from closed workbooks unless you have some of
the source workbooks open, but not all of the source workbooks open.
However, it's been my experience that Excel does not ever calculate from any
closed workbooks. Not only that, but any time it does try to, it just
returns the "#REF!" error messages within those cells. That's one of the
reasons why I have turned to VBA to control how things are done within Excel
when it comes to my production reports.

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

"RagDyer" wrote in message
...
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)

<<<"However, it's been my experience that Excel does *NOT EVER* calculate
from any closed workbooks."
(emphasis mine)

This is *absolutely* false!

And I'm posting an example here, not so much for your benefit, but strictly
for others who might come across this thread and might perchance believe
what you just said.

This will only take 1 cell on each of 2 WBs, so you might as well use 2 of
your already existing WBs for this demo.

Open both.
In one of them, enter 10 in say Z1.
In the other, say in Z2, enter:
=25*
THEN, navigate to the first WB, and click in Z1 (which contains the value
10),
Then hit <Enter.

This returns you to the WB with the formula.

The return of the formula is 250.
Save and close this WB.

In the other, *change* the value in Z1 from 10 to say 5.
Save and close this WB.

NOW, open the first.
You'll get a message saying that links exist to other WBs, and do you wish
to update them.
Answer *NO*.

Look at Z2.
The return is *still* 250.
You know that you changed the value from 10 to 5 in the other WB.
Now, click on:
<Edit <Links
Move the window so that you can see Z2.
Click on "Update Now".
And you'll see the value in Z2 change to 125,
Proving that XL is calculating with a value derived from a *closed* WB.

This is just an over simplification of an *existing feature* of XL.
Namely, the ability to glean data from closed WBs, when adhering to specific
procedures.
It doesn't matter where that closed WB is located, as long as you can access
it from your machine in the first place.
It could be a different folder, a different drive, a different machine
(server), or even in a different city.

If you had answered the opening "Link" question with a "Yes", all linked
formulas would have been re-calculated with the *presently existing* data
from all the linked WBs, whether they were opened or *closed*.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------





"Ronald Dodge" wrote in message
...
If you go to the contents of the Excel help file, which I have seen it in
XL97 as well as XL2K, which here's the path to it in XLXP:

Microsoft Excel Help
Creating and Correcting Formulas
Creating Links
Control when links are updated

Within there, you see under the sub content of "Links to other workbooks"
as
implying that it can calculate from closed workbooks unless you have some
of
the source workbooks open, but not all of the source workbooks open.
However, it's been my experience that Excel does not ever calculate from
any
closed workbooks. Not only that, but any time it does try to, it just
returns the "#REF!" error messages within those cells. That's one of the
reasons why I have turned to VBA to control how things are done within
Excel
when it comes to my production reports.

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

"RagDyer" wrote in message
...
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)