Trouble With LINKS

[Mike]

Good Morning All,
Using Excel 2002

I have several workbooks in which all data in cells come from
links of other workbooks which are all located in "My Documents". When I
open both workbooks I get the message box about the workbook containing
links to other data sources. When I click on to update the data the first
workbook takes a few seconds to update all the data and opens up the
workbook with all the data readable. However in the 2nd workbook, after
several seconds, it opens but none of the data is displayed. All the cells
have the #VALUE! error. This forces me to open up the source workbook(s) to
display the data.
I am trying to figure out why the first workbook opens with all
the updated data (without the source workbook(s) opening) while the 2nd
workbook does not update the data unless I open up the source workbook(s).
I would like to update all information without having to open the source
workbook(s). Any help would be appreciated. Thanks.

Mike

 

[Guest]

Hi

It's not a very precise answer but it might give you a clue and send you off
looking in the right direction!
Certain functions (I believe INDIRECT is one) will not work on closed
workbooks. The workbooks must be open for the function to operate. I'll see
if I can find more details for you - but it's a start!
Hope this helps!

 

[Mike]

Thanks Andy for your help. In the workbook that displays the data I have
two functions, =SUM and =AVERAGE. In the workbook that will not update they
all contain =COUNTIF so mabye that is one of the functions that you have to
have the workbook link open to see the data.
Mike

 

[Dave Peterson]

But there are equivalent formulas:

this formula:
=COUNTIF('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13,"asdf")
breaks when I close book1.xls.

But this formula will continue to work:
=SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13="asdf"))

(Except that you can't use the whole column with =sumproduct().)

 

[Mike]

Thanks Dave for your help. I changed the =countif to the =sumproduct
formula but it doesn't seem to work. It doesn't give me the #VALUE! but it
didn't calculate it corrrectly. I'm not sure what you mean by the whole
column, like an A:A formula? I want it to count the number of occurences in
$B$1:$B$30.
Also what would I use if i wanted it to find occurences between two numbers,
say 5 & 10. Would that still be a SUMPRODUCT formula?
TIA,
Mike

But there are equivalent formulas:

this formula:
=COUNTIF('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13,"asdf")
breaks when I close book1.xls.

But this formula will continue to work:
=SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet
3'!$A$1:$A$13="asdf"))

(Except that you can't use the whole column with =sumproduct().)

Thanks Andy for your help. In the workbook that displays the data I have
two functions, =SUM and =AVERAGE. In the workbook that will not update
they
all contain =COUNTIF so mabye that is one of the functions that you have
to
have the workbook link open to see the data.
Mike

 

[Mike]

I got it to work!! To get it to count between 5 and 10 I used:
=SUMPRODUCT(--('C:\Documents\excel\[Book1.xls]Sheet3'!$B$1:B$30>=5),--('C:\Documents\excel\[Book1.xls]Sheet3'!$B$1:B$30<=10))
Thanks Dave for getting me on the right track.
Mike

Thanks Dave for your help. I changed the =countif to the =sumproduct
formula but it doesn't seem to work. It doesn't give me the #VALUE! but
it didn't calculate it corrrectly. I'm not sure what you mean by the
whole column, like an A:A formula? I want it to count the number of
occurences in $B$1:$B$30.
Also what would I use if i wanted it to find occurences between two
numbers, say 5 & 10. Would that still be a SUMPRODUCT formula?
TIA,
Mike

But there are equivalent formulas:

this formula:
=COUNTIF('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13,"asdf")
breaks when I close book1.xls.

But this formula will continue to work:
=SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet
3'!$A$1:$A$13="asdf"))

(Except that you can't use the whole column with =sumproduct().)

Thanks Andy for your help. In the workbook that displays the data I
have
two functions, =SUM and =AVERAGE. In the workbook that will not update
they
all contain =COUNTIF so mabye that is one of the functions that you have
to
have the workbook link open to see the data.
Mike
<Andy B> wrote in message
Hi

It's not a very precise answer but it might give you a clue and send
you
off looking in the right direction!
Certain functions (I believe INDIRECT is one) will not work on closed
workbooks. The workbooks must be open for the function to operate.
I'll
see if I can find more details for you - but it's a start!
Hope this helps!

--
Andy.


Good Morning All,
Using Excel 2002

I have several workbooks in which all data in cells come
from
links of other workbooks which are all located in "My Documents".
When
I open both workbooks I get the message box about the workbook
containing
links to other data sources. When I click on to update the data the
first workbook takes a few seconds to update all the data and opens
up
the workbook with all the data readable. However in the 2nd
workbook,
after several seconds, it opens but none of the data is displayed.
All
the cells have the #VALUE! error. This forces me to open up the
source
workbook(s) to display the data.
I am trying to figure out why the first workbook opens
with
all the updated data (without the source workbook(s) opening) while
the
2nd workbook does not update the data unless I open up the source
workbook(s). I would like to update all information without having to
open the source workbook(s). Any help would be appreciated. Thanks.

Mike

 

[Dave Peterson]

If you post the formula you used, it would be easier to see.

Thanks Dave for your help. I changed the =countif to the =sumproduct
formula but it doesn't seem to work. It doesn't give me the #VALUE! but it
didn't calculate it corrrectly. I'm not sure what you mean by the whole
column, like an A:A formula? I want it to count the number of occurences in
$B$1:$B$30.
Also what would I use if i wanted it to find occurences between two numbers,
say 5 & 10. Would that still be a SUMPRODUCT formula?
TIA,
Mike

But there are equivalent formulas:

this formula:
=COUNTIF('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13,"asdf")
breaks when I close book1.xls.

But this formula will continue to work:
=SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet
3'!$A$1:$A$13="asdf"))

(Except that you can't use the whole column with =sumproduct().)

Thanks Andy for your help. In the workbook that displays the data I have
two functions, =SUM and =AVERAGE. In the workbook that will not update
they
all contain =COUNTIF so mabye that is one of the functions that you have
to
have the workbook link open to see the data.
Mike
<Andy B> wrote in message Hi

It's not a very precise answer but it might give you a clue and send
you
off looking in the right direction!
Certain functions (I believe INDIRECT is one) will not work on closed
workbooks. The workbooks must be open for the function to operate. I'll
see if I can find more details for you - but it's a start!
Hope this helps!

--
Andy.


Good Morning All,
Using Excel 2002

I have several workbooks in which all data in cells come
from
links of other workbooks which are all located in "My Documents".
When
I open both workbooks I get the message box about the workbook
containing
links to other data sources. When I click on to update the data the
first workbook takes a few seconds to update all the data and opens up
the workbook with all the data readable. However in the 2nd workbook,
after several seconds, it opens but none of the data is displayed.
All
the cells have the #VALUE! error. This forces me to open up the
source
workbook(s) to display the data.
I am trying to figure out why the first workbook opens with
all the updated data (without the source workbook(s) opening) while
the
2nd workbook does not update the data unless I open up the source
workbook(s). I would like to update all information without having to
open the source workbook(s). Any help would be appreciated. Thanks.

Mike