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
|