Trouble With LINKS

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

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.


"Mike" <[email protected]> wrote in message
news:c%nge.5987$Ay3.4021@lakeread06...
> 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
>
>

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 news:%[email protected]...
> 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.
>
>
> "Mike" <[email protected]> wrote in message
> news:c%nge.5987$Ay3.4021@lakeread06...
>> 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
>>
>>
>
>

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 wrote:
>
> 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 news:%[email protected]...
> > 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.
> >
> >
> > "Mike" <[email protected]> wrote in message
> > news:c%nge.5987$Ay3.4021@lakeread06...
> >> 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

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
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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 news:%[email protected]...
>> > 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.
>> >
>> >
>> > "Mike" <[email protected]> wrote in message
>> > news:c%nge.5987$Ay3.4021@lakeread06...
>> >> 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

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


"Mike" <[email protected]> wrote in message
news:1Hxge.9128$Ay3.3854@lakeread06...
> 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
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> 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 wrote:
>>>
>>> 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
>>> news:%[email protected]...
>>> > 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.
>>> >
>>> >
>>> > "Mike" <[email protected]> wrote in message
>>> > news:c%nge.5987$Ay3.4021@lakeread06...
>>> >> 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.



Mike wrote:
>
> 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
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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 news:%[email protected]...
> >> > 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.
> >> >
> >> >
> >> > "Mike" <[email protected]> wrote in message
> >> > news:c%nge.5987$Ay3.4021@lakeread06...
> >> >> 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

--

Dave Peterson