Thanks a lot for the answer, that really helped me out (I am a real
newbie on excel). Now I only got one similar problem left before my
day is made.
I use a function called SUM.IF (I think it is called that anyway (I am
using a swedish version where it is called SUMMA.OM)). It is a
function that takes 3 parameters:
- a region (bunch of cells) to evaluate
- a condition
- a region to sum (cells to be parameters to SUM).
===================================================================
Here is the thing...
I have 2 documents: myDoc.xls and linkingDoc.xls.
linkingDoc has got links to myDoc.
myDoc.xls looks like this in the columns M and N
M N
4 113
2 114
5 115
1 114
7 120
What I want to do is to go through column N searching for the string
"114" and when I find that, I want to add the value from column M on
the same row. So the example above would put 3 in my cell (in
linkingDoc.xls).
===================================================================
When I type
=SUM.IF('E:\myMap\[myDoc.xls]myCheet'!$N$8:$N$10;"=114";'E:\myMap\[myDoc.xls
]myCheet'!$M$8:$M$10)
...I get some illegal value text in the linkingDoc cell, but when I
open myDoc.xls the cell gets the values
===================================================================
When I type
=SUM.IF('E:\myMap\[myDoc.xls]myCheet'!$N$8:'E:\myMap\[myDoc.xls]myCheet'!$N$
10;"=114";'E:\myMap\[myDoc.xls]myCheet'!$M$8:'E:\myMap\[myDoc.xls]myCheet'!$
M$10)
...I get the #reference in the linkingDoc cell, but when I open
myDoc.xls the cell gets the values
===================================================================
Am I doing things really wrong way here?
Is there another way of achieving what I want?
Help on this one would also be really appreciated!
thanx for your time.
"Jim Rech" <
[email protected]> wrote in message
That's kind of an odd way to write an external formula. when I tried
something similar I also had a problem. But the conventional way seemed to
be okay:
SUM('E:\myMap\[myDoc]myCheet'!$M$8:$M$10)
--
Jim Rech
Excel MVP
|I get the problem when trying to SUM a bunch of cells from an external
| document. I dont get the problem though if I SUM just 2 cells.
|
| Example:
| this does not work (returns "#reference")
| SUM('E:\myMap\[myDoc]myCheet'!$M$8:'E:\myMap\[myDoc]myCheet'!$M$10)
|
| but the following works fine (returns the value)
| SUM('E:\myMap\[myDoc]myCheet'!$M$8:'E:\myMap\[myDoc]myCheet'!$M$9)
|
| the first example works only if I open the document that is referenced
| (so that only the documents basename is seen in the cell formula).
|
|
| (e-mail address removed) (marcus) wrote in message
| > I have excel2000 that I run on Win2000.
| >
| > I have an excel document A that links to other excel documents B, C,
| > ... I am having problem with updating the linked cells in A. These
| > cells says #reference.
| > If I open and close, lets say document B, then the cells referencing
| > this document will get their true values.
| >
| > The cells will also get their values if the linked documents are open
| > when I open document A.
| >
| > In short: Document A must have seen the linked documents open at some
| > point for the linked cells in A to get their values.
| >
| > Why is this??
