Home 
Search 
Today's Posts 
#1




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Paste is is copying in formula, but display is wrong.  Excel Discussion (Misc queries)  
What instead of an array formula?  Excel Discussion (Misc queries)  
Aligning Two Lists in Excel  Excel Discussion (Misc queries)  
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula?  Excel Discussion (Misc queries)  
VBA Import of text file & Array parsing of that data  Excel Discussion (Misc queries) 