Thanks for the suggestions guys, I'm going to run with the pivot table...

Jock


"CLR" wrote:

Another solution to problems like these, is to use code to fill your field
with the formulas you have that work, then in the same macro, do Copy
Pastespecial Values to delete the formulas, leaving only the
results.......whenever you want a "recalc" just fire the macro..........

Vaya con Dios,
Chuck, CABGx3




"Jock" wrote:

I have been asked to split down the entries in a post-logging spreadsheet
detailing how many entries of type 'A', 'B', 'C' and so on were input for
week 1, week2, week 3 etc of this year. There is a maximum of 33 different
types of post and, obviously, 52 weeks in the year. This means 1716
calculations! The formula below does the job where "AN$3" is the week number
and "$A3" is the post type:

SUMPRODUCT(--(1+INT((Chancery!$B$8:$B$9997-(DATE(YEAR(Chancery!$B$8:$B$9997),1,2)-WEEKDAY(DATE(YEAR(Chancery!$B$8:$B$9997),1,1))))/7)=AN$3)*(Chancery!$J$8:$J$9997=$A3))
This amount of calculations makes Excel grind to a halt when opening and
when the worksheet with the formulae has focus. I am therefore seeking an
alternative solution even if it's a code which only calculates the current
month rather than 52 weeks.
Any ideas?




--
tia

Jock