PDA

View Full Version : Balance Sheet, SumProduct and VBA, how to?



cortiz1bog
11-11-2005, 07:14 AM
There is a maximun size for a formula, that can run from VBA, insted of having the formula in the cell

Any way to get around?

I have a very large formula (see bellow), for a range in 5 colunms, but slows the workbook even with manual calcution

I am attaching the balance sheet(part of the workbook, without the formulas due to file-size), so you can get a better idea as to where the formula goes and maybe you all can give other suggestions.
I am running Office 2000

C4:C30

=SUMAPRODUCTO((E!$Q$183:$Q$9999=$A4)*(E!$M$183:$M$9999<$C$2)*1,(E!$S$183:$S$9999)+(E!$T$183:$T$9999))+SUMAPRODUCTO((E!$X$183:$X$99 99=$A4)*(E!$M$183:$M$9999<$C$2)*1,(E!$Z$183:$Z$9999)+(E!$AA$183:$AA$9999))+SUMAPRODUCTO((E!$AE$183:$A E$9999=$A4)*(E!$M$183:$M$9999<$C$2)*1,(E!$AG$183:$AG$9999)+(E!$AH$183:$AH$9999))+SUMAPRODUCTO((E!$M$183:$ M$9999<$C$2)*($A4=240802)*1,(E!$K$183:$K$9999))-SUMAPRODUCTO((E!$M$183:$M$9999<$C$2)*($A4=110500)*1,(E!$L$183:$L$9999))+SUMAPRODUCTO(('F'!$G$184:$G$9999<$C$2)*($A4=110500)*1,('F'!$L$184:$L$9999))-SUMAPRODUCTO(('F'!$G$184:$G$9999<$C$2)*($A4=410500)*1,('F'!$J$184:$J$9999))-SUMAPRODUCTO(('F'!$G$184:$G$9999<$C$2)*($A4=240801)*1,('F'!$K$184:$K$9999))+$K4


D4: D30

=SUMAPRODUCTO((E!$Q$183:$Q$9999=$A4)*(E!$M$183:$M$9999>=$C$2)*(E!$M$183:$M$9999<=$F$2)*1,(E!$S$183:$S$9999)+(E!$T$183:$T$9999))+SUMAPRODUCTO((E!$X$183:$X$9 999=$A4)*(E!$M$183:$M$9999>=$C$2)*(E!$M$183:$M$9999<=$F$2)*1,(E!$Z$183:$Z$9999)+(E!$AA$183:$AA$9999))+SUMAPRODUCTO((E!$AE$183:$ AE$9999=$A4)*(E!$M$183:$M$9999>=$C$2)*(E!$M$183:$M$9999<=$F$2)*1,(E!$AG$183:$AG$9999)+(E!$AH$183:$AH$9999))+SUMAPRODUCTO((E!$M$183: $M$9999>=$C$2)*(A4=240802)*(E!$M$183:$M$9999<=$F$2)*1,(E!$K$183:$K$9999))+SUMAPRODUCTO(('F'!$G$184:$G$9999>=$C$2)*('F'!$G$184:$G$9999<=$F$2)*($A4=110500)*1,('F'!$L$184:$L$9999))+$L4


E4:E30

=SUMAPRODUCTO((E!$M$183:$M$9999>=$C$2)*(E!$M$183:$M$9999<=$F$2)*($A4=110500)*1,(E!$L$183:$L$9999))+SUMAPRODUCTO(('F'!$G$184:$G$9999>=$C$2)*('F'!$G$184:$G$9999<=$F$2)*($A4=410500)*1,('F'!$J$184:$J$9999))+SUMAPRODUCTO(('F'!$G$184:$G$999 9>=$C$2)*('F'!$G$184:$G$9999<=$F$2)*($A4=240801)*1,('F'!$K$184:$K$9999))+SUMAPRODUCTO((T!$H$211:$H$9999>=$C$2)*(T!$H$211:$H$9999<=$F$2)*($A4=236700)*1,(T!$U$211:$U$9999))+SUMAPRODUCTO((T!$H$211:$H$9999>=$C$2)*(T!$H$211:$H$9999<=$F$2)*($A4=130565)*1,(T!$R$211:$R$9999))+SUMAPRODUCTO((T!$H$211:$H$9999>=$C$2)*(T!$H$211:$H$9999<=$F$2)*($A4=110500)*1,(T!$T$211:$T$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=240802)*1,(I!$H$3:$H$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=240803)*1,(I!$I$3:$I$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=110500)*1,(I!$B$3:$B$9999))



K4:K30

=SUMAPRODUCTO((T!$H$211:$H$9999<$C$2)*($A4=240803)*1,(T!$U$211:$U$9999))-SUMAPRODUCTO((T!$H$211:$H$9999<$C$2)*($A4=236700)*1,(T!$U$211:$U$9999))+SUMAPRODUCTO((T!$L$211:$L$9999=$A4 )*(T!$H$211:$H$9999<$C$2)*1,(T!$P$211:$P$9999)+(T!$Q$211:$Q$9999)+(T!$S$211:$S$9999))-SUMAPRODUCTO((T!$H$211:$H$9999<$C$2)*($A4=130565)*1,(T!$R$211:$R$9999))-SUMAPRODUCTO((T!$H$211:$H$9999<$C$2)*($A4=110500)*1,(T!$T$211:$T$9999))+SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=236700)*1,(I!$F$3:$F$9999))+SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=240801)*1,(I!$G$3:$G$9999))-SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=240802)*1,(I!$H$3:$H$9999))-SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=240803)*1,(I!$I$3:$I$9999))+SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=511510)*1,(I!$J$3:$J$9999))+SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=530590)*1,(I!$K$3:$K$9999))-SUMAPRODUCTO((I!$C$3:$C$9999<$C$2)*($A4=110500)*1,(I!$B$3:$B$9999))


L4:L30

=SUMAPRODUCTO((T!$H$211:$H$9999>=$C$2)*(T!$H$211:$H$9999<=$F$2)*($A4=240803)*1,(T!$U$211:$U$9999))+SUMAPRODUCTO((T!$L$211:$L$9999=$A 4)*(T!$H$211:$H$9999>=$C$2)*(T!$H$211:$H$9999<=$F$2)*1,(T!$P$211:$P$9999)+(T!$Q$211:$Q$9999)+(T!$S$211:$S$9999))+SUMAPROD UCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=236700)*1,(I!$F$3:$F$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=240801)*1,(I!$G$3:$G$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=511510)*1,(I!$J$3:$J$9999))+SUMAPRODUCTO((I!$C$3:$C$9999>=$C$2)*(I!$C$3:$C$9999<=$F$2)*($A4=530590)*1,(I!$K$3:$K$9999))


All ideas and suggestions are welcome.

Thanks

Cesar

malik641
11-12-2005, 08:18 PM
Hi Cesar :hi:

A couple questions for you:
Are these formulas in Spanish?
The formulas refer to sheets "E", "F", "T", "I", etc... what data are in these sheets????

And a suggestion:
If you can't use these formulas because of how many characters there are, you can create Defined Names using Data Validation for all those ranges you have. It would reduce the amount of characters significantly.

cortiz1bog
11-13-2005, 06:13 AM
Thanks Joseph,
The formulas is in spanish, SumaProducto = SumProduct...
I cut to the minimun the names of the sheets, as much as I could:
E = Expenses-Debits
F = Invoices-Credits
T = Payrol and taxable service providers
I = Salex tax, employee taxes and goverment taxes.

I hardly ever use Data Validation with Names for the ranges, sounds goods, could you show quick example.
Thanks
Cesar

malik641
11-13-2005, 07:55 AM
Thanks Joseph,
The formulas is in spanish, SumaProducto = SumProduct...
I cut to the minimun the names of the sheets, as much as I could:
E = Expenses-Debits
F = Invoices-Credits
T = Payrol and taxable service providers
I = Salex tax, employee taxes and goverment taxes.

I hardly ever use Data Validation with Names for the ranges, sounds goods, could you show quick example.
Thanks
CesarOops! Data Validation :doh:...I meant Insert --> Name --> Define...

Here's two examples:
http://www.homeandlearn.co.uk/ME/mes9p2.html

http://www.j-walk.com/ss/excel/tips/tip26.htm

Keep the names short, though. Make the meaningful, of course, but shorter than the ranges themselves to save space.

Zack Barresse
11-13-2005, 11:41 AM
Check out this read (by xld) ... http://www.xldynamic.com/source/xld.Names.html