I would of loved to use pivot tables for this unfortunately the business
users insist on being able to do "offline" adjustments ( the + L14 in the EG
below) that pivot tables cannot handle

Regards

Reg

"Frank Kabel" wrote in message
...
Hi
have you ttried using a pivot table for this

"Reg Besseling" wrote:

Hi All

I had to create a report in Excel that was dynamic and had no VBA.

The source for the report is an Excel database 8000 rows long and 9
columns
wide.

The only way i could think of to do the report was to use Array formulas.

I tried sumproduct but could not successfully embed if formulas in the
sumproduct formula

I have over 1000 of these array formulas

the report works but take in excess of 20 minutes to open / do a recalc.

I have been told this is too long but what else can i use??

here is an EG of one of the formulas

=-SUM(IF($B8="",(MORMTD.xls!$Z$2:$Z$12000=$B8),(MORM TD.xls!$D$2:$D$12000=$B8))*IF($B9="",(MORMTD.xls!$ Z$2:$Z$12000=$B9),(MORMTD.xls!$G$2:$G$12000=$B9))* IF($B5="",(MORMTD.xls!$Z$2:$Z$12000=$B5),(MORMTD.x ls!$A$2:$A$12000=$B5))*(MORMTD.xls!$B$2:$B$12000=" 0005")*(MORMTD.xls!$F$2:$F$12000=B12)*IF($B7="",(M ORMTD.xls!$Z$2:$Z$12000=$B7),(LEFT(MORMTD.xls!$E$2 :$E$12000,LEN($B7))=$B7))*IF($B6="",(MORMTD.xls!$Z $2:$Z$12000=$B6),(MORMTD.xls!$C$2:$C$12000=$B6))*M ORMTD.xls!$I$2:$I$12000)+L14

TIA

Reg Besseling