Link to Workbook
 

Hi All,

Quick question - If I have a statistical calculations spreadsheet that is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without VBA?

Thanks

Andy

No, the file doesn't have to be open. In fact, if the file is open, the linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I like to
have both files open and use the mouse to point and click at the "sending"
cell. Then excel does the heavy work and builds the formula with the correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without VBA?

Thanks

Andy

--

Dave Peterson

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I like
to
have both files open and use the mouse to point and click at the "sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without
VBA?

Thanks

Andy

--

Dave Peterson

Simple links should update. But there are worksheet functions that won't work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I like
to
have both files open and use the mouse to point and click at the "sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I like
to
have both files open and use the mouse to point and click at the "sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that
is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

I think it would depend on the ranges used within each =sumproduct() formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I like
to
have both files open and use the mouse to point and click at the "sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that
is
linked to a seperate data sheet - does the data sheet have to be open in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I am sure that will be fine, if I build some vba to select the relevent data
sheet, open it, point the statistics sheet at the relevent data sheet, then
update it all.

Thanks for your input - I bet I would have been looking for hours trying to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that
is
linked to a seperate data sheet - does the data sheet have to be open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the relevent data
sheet, open it, point the statistics sheet at the relevent data sheet, then
update it all.

Thanks for your input - I bet I would have been looking for hours trying to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open, the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet that
is
linked to a seperate data sheet - does the data sheet have to be open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thanks for the suggestion but the aim of me seperating the calculations is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Turn calculation to manual, do all your changes, turn calculation back to
automatic so that it only recalcs once???



Andibevan wrote:

Thanks for the suggestion but the aim of me seperating the calculations is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly - often
looking at 5 criteria. I have about 100 of these forumlas and if this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files, I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Another good idea, but it would probably not be too suitable as on the data
sheet, some of the columns are calculations (age is calculated from
today()-start date) and therefore needs to remain on autocalculate - from my
understanding you can only turn calculations on and off for the entire
workbook?




"Dave Peterson" wrote in message
...
Turn calculation to manual, do all your changes, turn calculation back to
automatic so that it only recalcs once???



Andibevan wrote:

Thanks for the suggestion but the aim of me seperating the calculations is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly -
often
looking at 5 criteria. I have about 100 of these forumlas and if this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not
as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files,
I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with
the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Actually, calculation is an application setting.

And if you're using xl2002+, you can turn off calculation on a worksheet by
worksheet basis.


But if you turn calculation off while entering data, you can turn it back on
when you're ready and see the formulas re-evaluate.

Andibevan wrote:

Another good idea, but it would probably not be too suitable as on the data
sheet, some of the columns are calculations (age is calculated from
today()-start date) and therefore needs to remain on autocalculate - from my
understanding you can only turn calculations on and off for the entire
workbook?

"Dave Peterson" wrote in message
...
Turn calculation to manual, do all your changes, turn calculation back to
automatic so that it only recalcs once???

Andibevan wrote:

Thanks for the suggestion but the aim of me seperating the calculations is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly -
often
looking at 5 criteria. I have about 100 of these forumlas and if this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is not
as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other files,
I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with
the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I think I will use the functionality of xl2002 as I believe that is what we
are using.

Thanks for all your assistance

Andy

"Dave Peterson" wrote in message
...
Actually, calculation is an application setting.

And if you're using xl2002+, you can turn off calculation on a worksheet by
worksheet basis.


But if you turn calculation off while entering data, you can turn it back on
when you're ready and see the formulas re-evaluate.

Andibevan wrote:

Another good idea, but it would probably not be too suitable as on the
data
sheet, some of the columns are calculations (age is calculated from
today()-start date) and therefore needs to remain on autocalculate - from
my
understanding you can only turn calculations on and off for the entire
workbook?

"Dave Peterson" wrote in message
...
Turn calculation to manual, do all your changes, turn calculation back to
automatic so that it only recalcs once???

Andibevan wrote:

Thanks for the suggestion but the aim of me seperating the calculations
is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the
relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours
trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly -
often
looking at 5 criteria. I have about 100 of these forumlas and if
this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that
approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is
not
as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is
open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other
files,
I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with
the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations
spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

You need VBA to toggle this setting.

Worksheets("sheet1").EnableCalculation = False

But this disables calculation on that sheet--even for the most minor of
changes. (Put =rand() or =today() in a cell to see how it reacts.)


Andibevan wrote:

I think I will use the functionality of xl2002 as I believe that is what we
are using.

Thanks for all your assistance

Andy

"Dave Peterson" wrote in message
...
Actually, calculation is an application setting.

And if you're using xl2002+, you can turn off calculation on a worksheet by
worksheet basis.

But if you turn calculation off while entering data, you can turn it back on
when you're ready and see the formulas re-evaluate.

Andibevan wrote:

Another good idea, but it would probably not be too suitable as on the
data
sheet, some of the columns are calculations (age is calculated from
today()-start date) and therefore needs to remain on autocalculate - from
my
understanding you can only turn calculations on and off for the entire
workbook?

"Dave Peterson" wrote in message
...
Turn calculation to manual, do all your changes, turn calculation back to
automatic so that it only recalcs once???

Andibevan wrote:

Thanks for the suggestion but the aim of me seperating the calculations
is
that they slow down the updating of the data sheet as every time a date
changes it recalculates everything on the sheet.

"Dave Peterson" wrote in message
...
Another option may be to build your =sumproduct() formulas in the closed
workbook--if you don't need something from the open workbook.

Then just retrieve those already calculated values.

Andibevan wrote:

I am sure that will be fine, if I build some vba to select the
relevent
data
sheet, open it, point the statistics sheet at the relevent data sheet,
then
update it all.

Thanks for your input - I bet I would have been looking for hours
trying
to
get the updates to work.

Ta

Andi

"Dave Peterson" wrote in message
...
I think it would depend on the ranges used within each =sumproduct()
formula,
too.

The only way I know to speed things up is to open that other workbook.

Andibevan wrote:

Dave,

Thanks for your comment - I am using sumproduct forumulas mainly -
often
looking at 5 criteria. I have about 100 of these forumlas and if
this
is
included in the main workbook it slows it down immensley.

Ta

Andi

"Dave Peterson" wrote in message
...
Simple links should update. But there are worksheet functions that
won't
work
with closed files.

=indirect(), =sumif(), =countif()

are a couple.

Do you use those in your formulas?

There might be alternative solutions.

Andibevan wrote:

I am linking to named ranges in a seperate workbook so that
approach
unfortunately won't work.

I am finding that if I open the statistical sheet and then press
update -
nothing happens and all the values say #value.

When I open up the data sheet the values all calculate.

From my understanding of your mail the behaviour of my sheet is
not
as
you
describe.

Any ideas?

"Dave Peterson" wrote in message
...
No, the file doesn't have to be open. In fact, if the file is
open,
the
linked
formulas will be reevaluated with the next calculation.

When I'm building those formulas that contain links to other
files,
I
like
to
have both files open and use the mouse to point and click at the
"sending"
cell. Then excel does the heavy work and builds the formula with
the
correct
syntax.

Andibevan wrote:

Hi All,

Quick question - If I have a statistical calculations
spreadsheet
that
is
linked to a seperate data sheet - does the data sheet have to be
open
in
order to use the EditLinksUpdate?

I want to try and link to a closed worksheet - is this possible
without
VBA?

Thanks

Andy

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson