Not sure how sensitive your data is, but I'll happily take a stab at
matching the output from any of your reports with a Pivot Table report if
you want. You would need to take the NOSPAM bit out of my email addy, and
give me an idea of an existing report format.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Cornelius" wrote in message
...
Looks interesting. I'm not sure it will give me the detail I'm looking
for,
but it is definitely worth trying, thanks Ken.
On another note, I suspect I can achieve some of the results I was looking
for by eliminating some of the axes (I'll just calculate the possible
choices
on the data field first), and using OFFSET to redfine the ranges for time
period (i.e.
IF("Quarterly",OFFSET(A1:Z1,x),IF("Monthly",OFFSET (A1:Z1,y)...
Thanks for all the help. If someone has an epiphany, I'd love to know the
solution.
ccc
"Ken Wright" wrote:
Cornelius, from the sound of it you appear to be doing the kind of
analysis
that pivot tables were designed for. Have you looked at using those for
your data at all?
http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--
"Cornelius" wrote in message
...
Hi all. I've been developing a sheet that measures numerous aspects
of my
business and allows any set of parameters to be displayed at a single
time.
For instance, one could look at sales to New York on a weekly basis in
2004
through a particular channel or customer, then switch these parameters
using
validated cells to Chicago quarterly 2004 all business.
I've created all the sets of vlookups for a SUMPRODUCT to look at,
each
corresponding to a large data grid that has the appropriate categories
(dollars or pounds, weeks, years, customer category, region, etc.).
Here
is
the formula:
=SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7:
$E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11
6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D
ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865)
where Data!M7:FN865 is the data grid. The NOT functions eliminate
certain
areas that shouldn't be calculated (i.e. looking at New York you
ignore
Chicago, LA and Miami, but looking at all business you ignore
nothing).
This formula returns N/A. If I reduce the ranges to be used or
ignored
(make $C$88:$H$88 just $C$88) I get a value. How do I overcome this?
I
have
used ranges like this in the past succesfully, but never so many.