to many formula

K

kev carter

hi

i have a worksheet with a lot of data on it
column A contains the date
column B contains an area
column C contains the fault
column D contain a quantity


A B C
D
1/4/04 stores damaged
10
7/4/04 bins rust
60



31/12/04 floor wet
100
what i want to do is return quantity for a date range(one week)
once the week range is checked the area and fault are checked
i could have 12 faults for one area
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas
the formula i am using is
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1:D100)

anyone got any ideas how i can
i counted over 6000 formulas
can this be done in VBA or anyother method(not pivot table, i need to retain
the values for 12 months)

Thanks in advance


kevin
 
F

Frank Kabel

Hi
why aren't pivot tables an option?. If you group your values
accordingly in a pivot table this should do.
another way would be using formulas but then you have to explain how
you would like the outcome result?
 
A

AlfD

Hi!

I agree with Frank : a good use of pivot tables.

I got the impression you had a worry about your data. A pivot tabl
only reads it: it doesn't change it.


Al
 
K

kev carter

Morning Frank
I have not considered Pivot tables for two reasons
first is I don't fully understand them
second is I need to retain the values for 12 months on a weekly range
I believe a pivot table requires manual input to update

the user enters data into a database using an input page.
the faults only get recorded when they occur against an op number
a pivot table only shows the faults and op numbers that have values against
them (I need the headers to show all the time)
you suggested grouping values ??


I appreciate your help

tanks

kevin
 
F

Frank Kabel

Hi
- A pivot table will leave yoiur source data untouched. that is it
creates a new worksheet with the aggregated data in it
- You could set the pivot table to automatic update or just hit the
Refresh button. I assume that if you would put all this into formulas
(approx. 6000 with a large range) youi have to set the calculation mode
to manual :) so in this case you also have to do a manual refresh.

The second paragraph is not that clear for me as I did not fully
understand your data layout. If you like you can do the following:
- send me a file with your data (an extract of it)
- describe the desired output (manually create it in your table with
the column/row headers)

I'll take a look at it and see if I could create a pivot table based on
this
email: frank[dot]kabel[at]freenet[dot]de
 
D

Debra Dalgleish

A pivot table can summarize your data, and include items for which there
is no data. There are pivot table instructions and links here:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

When you create the pivot table, add Date and Area to the Row area, add
Fault to the Column area, and add Quantity to the data area, where it
will become Sum of Quantity.

Right-click on the Date field button
Choose Group and Show Detail> Group
Select to Group by Days, and set the number of Days to 7
Click OK

Right-click on the Area button
Choose Field Settings
Add a check mark to 'Show items with no data'
Click OK
 
Top