Link to Workbook

[Andibevan]

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 Edit>Links>Update?

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

Thanks

Andy

 

[Dave Peterson]

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]

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?

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.

 

[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]

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

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.

 

[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]

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

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.

 

[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]

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.

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.

 

[Dave Peterson]

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

 

[Andibevan]

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?




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

 

[Andibevan]

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

Thanks for all your assistance

Andy

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.

 

[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.)