I have a workbook that is a general summary sheet. I have automated it by using array formulas but this slowed down the spreadsheet alot.
I was hoping there is a way to speed this up with VBA.
the way the sheet works is i have written macros that bring in all the data from other workbooks, these would be the sub-summary sheets. and i place it off to the side in my worksheet.
the data from the subsummary sheets brings the following columns.
page #, Split #, Item #, Quantity, and Info
the Item #'s repeat many times
the General summary sheet combines any item # that are the same and adds the quantities together. I then have vba place this to the left of the data i brought in first. this gives me a list of item numbers to go in the general summary. now then columns that make up the general summary sheet are labeled by page numbers. my array formula is summing the quantities for a certain item # for that page.
this is the forumla that im using [array].
=CEILING(SUM(IF(($BX:$BX=$AQ24)*($BV:$BV=AV$23)*($BZ:$BZ=$AS24),$BY:$BY,"")),1)
My workbook has alot going on so i made (and attached) a sample one to make it easier to understand.
below is the folmula I use in the sample workbook i attached (same as above but with different references)
=CEILING(SUM(IF(($AE:$AE=$A25)*($AC:$AC=F$23)*($AG:$AG=$C25),$AF:$AF,"")),1)
this formula is in cell F25
is it possible to speed this up with VBA somehow or any suggestions? i know changing the ranges to not use the whole column would speed it up but the number of items can change from project to project.
thanks
JD
Bookmarks