What instead of an array formula part 2
 

Hi all

Ive posted this question before (orignal post below) but i did not describe
my problem correctly so i will try again.

Our reporting system will generate a "RAW" Excel file, the excel document i
have created uses the formula below to add up figures and give a total for a
particular mix of categories.

e.g. of the RAW data headings

Company Type DataType Branch Division Dept Sub Dept
Amount

this data is then "pivoted" and filtered with the array formula

The rows are determined by the type col
The Cols are determined by a combination of Type and DataType

in addition there may be some adjustments that have to be made to the
figures if the adjustments are captured into the final sheet the figures
must immediately change

I was told not to use vba or macros.

If there is somewhere to post/upload my sheets please let me know and ill do
it



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

Hi
if you can't use pivot tables or macros I don't see a better solution

--
Regards
Frank Kabel
Frankfurt, Germany

"Reg Besseling" schrieb im Newsbeitrag
...
Hi all

Ive posted this question before (orignal post below) but i did not
describe
my problem correctly so i will try again.

Our reporting system will generate a "RAW" Excel file, the excel
document i
have created uses the formula below to add up figures and give a
total for a
particular mix of categories.

e.g. of the RAW data headings

Company Type DataType Branch Division Dept Sub
Dept
Amount

this data is then "pivoted" and filtered with the array formula

The rows are determined by the type col
The Cols are determined by a combination of Type and DataType

in addition there may be some adjustments that have to be made to the
figures if the adjustments are captured into the final sheet the
figures
must immediately change

I was told not to use vba or macros.

If there is somewhere to post/upload my sheets please let me know and
ill do
it



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$1200
0=$B8))*IF($B9="",(MORMTD.xls!$Z$2:$Z$12000=$B9),( MORMTD.xls!$G$2:$G$12
000=$B9))*IF($B5="",(MORMTD.xls!$Z$2:$Z$12000=$B5) ,(MORMTD.xls!$A$2:$A$
12000=$B5))*(MORMTD.xls!$B$2:$B$12000="0005")*(MOR MTD.xls!$F$2:$F$12000
=B12)*IF($B7="",(MORMTD.xls!$Z$2:$Z$12000=$B7),(LE FT(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))*MORMTD.xls!$I$2:$I$12000 )+L14

TIA

Reg Besseling

Thanks for the conformation


"Frank Kabel" wrote in message
...
Hi
if you can't use pivot tables or macros I don't see a better solution

--
Regards
Frank Kabel
Frankfurt, Germany

"Reg Besseling" schrieb im Newsbeitrag
...
Hi all

Ive posted this question before (orignal post below) but i did not
describe
my problem correctly so i will try again.

Our reporting system will generate a "RAW" Excel file, the excel
document i
have created uses the formula below to add up figures and give a
total for a
particular mix of categories.

e.g. of the RAW data headings

Company Type DataType Branch Division Dept Sub
Dept
Amount

this data is then "pivoted" and filtered with the array formula

The rows are determined by the type col
The Cols are determined by a combination of Type and DataType

in addition there may be some adjustments that have to be made to the
figures if the adjustments are captured into the final sheet the
figures
must immediately change

I was told not to use vba or macros.

If there is somewhere to post/upload my sheets please let me know and
ill do
it



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$1200
0=$B8))*IF($B9="",(MORMTD.xls!$Z$2:$Z$12000=$B9),( MORMTD.xls!$G$2:$G$12
000=$B9))*IF($B5="",(MORMTD.xls!$Z$2:$Z$12000=$B5) ,(MORMTD.xls!$A$2:$A$
12000=$B5))*(MORMTD.xls!$B$2:$B$12000="0005")*(MOR MTD.xls!$F$2:$F$12000
=B12)*IF($B7="",(MORMTD.xls!$Z$2:$Z$12000=$B7),(LE FT(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))*MORMTD.xls!$I$2:$I$12000) +L14

TIA

Reg Besseling