Our group couldn't handle Pivot Tables (as much as I have tried!), plus we
need to print these for 15 monthly status reports (linked to Word).
So how would I create helper cells for arrays and the dynamic named ranges?
I counted and I think there are about 4500 array formulas (each month for a
5 year range for 5 or 6 builders on each worksheet x 15 worksheets) that are
almost identical (the only thing that changes is the current month date
range search). The array searches by Subdivision, then builder, then start
and end of month range. There are 15 sheets that are duplicates of each
other separating each subdivision. Each array includes the dynamic named
ranges for subd, builder, start and end date.
If you have any suggestions, it would be greatly appreciated! I have been
working to improve this for several years now!
Does your FastExcel program work on other users computers if I run it to
improve performance and then it is opened by another computer? In other
words, can I generate improved workbooks and then share them or do they have
to have the add-in to run some of the features?
Thanks!
"Charles Williams" wrote in message
...
OFFSET is volatile. This means that every formula that contains OFFSET or
is
dependent on a formula that contains OFFSET will always calculate at every
recalculation.
COUNTA is quite slow because it scans every cell in the used range of the
column you are counting.
Array formula are quite slow because each formula requires the creation
and
calculation of virtual columns/rows, and all of the formulae in these
virtual columns/rows have to be calculated each time the array formulae is
calculated.
The solution is one or more of
- use a completely different approach (ie pivot tables or do your
filtering
in ACCESS or ...)
- minimise the amount of repeated calculation by using helper
cells/columns
(only do your COUNTA once, only do Database!$C$3:$C$2500=$B$3 once etc)
- sort your data and do subset calculations
- use more efficient formulae (DSUM, SUMPRODUCT, ...)
regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
"Karl Burrows" wrote in message
...
I posted this earlier, but it went in a different direction...
I have several workbooks where I replaced array formulas with named
ranges
to pull data from a tab that imports data from an Access database.
There
were several thousand of these formulas. An example would be:
=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=E$9)*(Date<F$9),(Count),0)),""),"")
I created the names since I wanted to have a dynamic range of values to
search since the database would return varying rows of data depending on
what was input in the database:
=OFFSET(Database!$E$3,0,0,COUNTA(Database!$E:$E),1 )
When I replaced the original formulas:
=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Database!$C$ 3:$C$2500=$B$3)*(Database!
$D$3:$D$2500=$B28)*(Database!$E$3:$E$2500=E$9)*(D atabase!$E$3:$E$2500<=F$9)
,(Database!$E$3:$E$2500),0)),""),""))
with the named ranges, the workbooks have ground to a halt. It now
takes
forever for them to update (they weren't speed demons in the first place
with so many arrays) and Macros run incredibly slow (5 minutes or more
to
run!).
Did I do something wrong? Are there limitations to named ranges? Is
there
something with a dynamically named range that would create this problem?
I
thought I was trying to make the workbooks more efficient and allow for
an
expanding range of data without having to 'anticipate' how many rows to
include in the array making less monitoring for me.
Help! Thanks!