Waste Report charting (2002)

Topic

New Reply

We are trying to develope a report that tracks waste from our paper coating operation. We want to report this based upon product and week number (example product hqm for weeks 1-37). I have a spreadsheet where our plant manager enters the date, roll length in good material out, and I then calculate the % and plot this against a target. I know how to determin the week number and have a formula for instances where we coat the same product 2 or 3 times for that week number.

I am trying to figure out how to separate out 2006 data and report this from 2006+2007 combined data.

Any ideas? The final chart would be a simple column of week number (1-52) vs either lineal feet or % waste for year 2006 and a separate chart for 2007.

Reply | Quote

Replies

You could add an extra column with formulas that return the year of the dates.
Create a chart based on all rows.
Set an AutoFilter on the data. When you select 2006 or 2007 from the filter dropdown for the Year column, the chart will be updated accordingly.

Reply | Quote

How are the different products entered?
Are the products entered on different sheets?
Are they in different workbooks?
Are the entries in date order by rows?

zeddy

Reply | Quote

Hans, I was thinking along a similar line.

Zeddy,
Different products have their own tabs (sheets)
Same workbook
Entries are by date in rows.

Reply | Quote

Ok, I think I have figured a few things out.

In my waste report for product “abc” i have the following:
Column a – date of manufacture
Column b – input quantity
Column c – output of good material
Column d – the difference
Column e- the % waste for that run

From this data, I then have a dynamic chart that updates itself as new coating runs are added.

Way off screen, i have a column with the following formula =if(year(a4)=2006,weeknum(a4,2),””) and in the column next to it a similar formula to give the corresponding waste quantity. In the next 2 columns, I have similar formula spitting out 2007 data. from this I will create another dynamic chart.

While looking at the 2006 data, I noticed week 40 had 2 coating runs that week.

I am trying to figure out a formula that will combine these quanities for that week.
The only thing that I have come up with is: =IF(YEAR($A4)=2006,IF($P4=$P3,D3+D4,D4),””)

any ideas would be greatly appreciated.

Reply | Quote

It would be helpful if you could attach a stripped down copy of your workbook (with fake data if necessary).

Reply | Quote