Remember Me?

 Reg Besseling Posts: n/a 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

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

 Frank Kabel Posts: n/a 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

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

 Reg Besseling Posts: n/a 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

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Matt Excel Discussion (Misc queries) 2 December 7th 04 08:37 PM Reg Besseling Excel Discussion (Misc queries) 3 December 6th 04 01:55 PM Rich Excel Discussion (Misc queries) 2 December 4th 04 05:44 PM Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 11:29 AM Dennis Excel Discussion (Misc queries) 4 November 28th 04 10:20 PM

All times are GMT +1. The time now is 02:25 AM. Copyright ©2004-2020 ExcelBanter.