Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 04, 02:03 PM
Reg Besseling
 
Posts: n/a
Default 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


  #2   Report Post  
Old December 9th 04, 05:56 PM
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
Old December 10th 04, 08:35 AM
Reg Besseling
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste is is copying in formula, but display is wrong. Matt Excel Discussion (Misc queries) 2 December 7th 04 09:37 PM
What instead of an array formula? Reg Besseling Excel Discussion (Misc queries) 3 December 6th 04 02:55 PM
Aligning Two Lists in Excel Rich Excel Discussion (Misc queries) 2 December 4th 04 06:44 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 12:29 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 09:16 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017