Mutiple tabs showing same data until F9

[Mrid]

Hi have 2 workbooks making up a staff roster. Workbook 1 contains all the shifts, employee, shift descriptions etc and pivot tables for reporting.
Workbook 2 has worksheets that use the data from a worksheet in Workbook1 to visually show the roster. Each worksheet displays 2 weeks of information i.e 1st feb to 14th feb. The ‘Key’ (to use a database term) getting the data from workbook 1 is made from day of the month, week 1 or 2 and cell reference, so would look like 71c4 for someone at 7thday, 1stweek position c4.
I wrote a VBA function [=fillCell1(CELL("address",INDIRECT(ADDRESS(ROW(),COLUMN()))))] for every cell that has someone rostered.
It
A: generates key from cell, date etc
B: uses key to find data in workbook1
C: Decides what to display using built in rules
D: Displays the result in the correct cell

Issues:
The biggie is when you display a 2 week period tab and then switch to another 2 week period tab it displays the same data as the other tab until you do a F9 refresh. Then all the sheets show the same data of the sheet that was just refreshed. A refresh takes about 1min on a dual core machine.. Have made multiple modules of the function renamed for each sheet but still the same.
Without diving into code is this a common problem?

[MarvinP]

Hi Mrid and welcome to the forum,

Recalc everything takes a lot of time, depending on what kind of formulas you have. Array Formulas are time hogs. Read DonkeyOte signature line about Volitile Functions also as taking longer to calculate. at http://www.decisionmodels.com/calcsecretsi.htm

Did you know you can do a Range.Calculate so you might not need to recalc the entire workbook but only the part you need updates?

It might also be possible to Refresh a single Pivot Table (I'm reading the Object Browser) so you don't have to refresh/recalc everything.

More posibilities might be in speeding up your code.

[DonkeyOte]

Read DonkeyOte signature line about Volitile Functions also as taking longer to calculate.

I've seen the above point made a couple of times recently and to clarify:

Volatile functions do not take longer to calculate per se
(after all OFFSET is one of the fastest functions around)

Volatile functions can however generate potentially significant calculation overhead given they are susceptible to recalculating more often. That recalculation can in turn cascade through a model by means of association.

For the above reasons most (not all) would advocate non-volatile methods - even if slightly slower.
Premise being that it is better to use a formula that takes 0.5 seconds to calculate as and when necessary as opposed to a formula that takes 0.25 seconds to calculate but which recalculates with every volatile action (ie irrespective of necessity)

The above is something of a broad point given there are many other factors that will directly affect the impact of volatile constructs
(calculation mode, no. of volatile actions, no. of volatile functions, no. of dependants etc...)

[Roger Govier]

Hi

Does the data alter, after the key has been created?

If not, then create the key with your code, calculate the range of cells where that code has been posted to ensure that all the cells have the correct latest value, then Copy>Paste Special >xlvalues back over the range to replace the formulae.

That way, potentially slow formulae are eliminated from your workbook.

If the data does alter, then providing you have some event code to trigger off your code, the keys can be re-created, then "fixed" again