Solved: Chart from a list of cells with count of numbers as data

[mperrah]

Hello again,

I have to generate a report and anticipating my boss is going to want it often I need to automate as much as possible.

I do a quality control check on a job and fill a spread sheet. The line item that is out of code I write in a comment field. There are 26 fields that may be used. There may be multiple line numbers entered in the comment cell seperated with a comma and a space. I need to pie chart the percentage of each occurence of the line items failing.

For example, if 5 jobs had a fail on item 30 and no other item was an issue the pie would show 100% for item "30". If the jobs had other line items fail they would show the percentage of the total failed items.

The date range for the pie chart is one week at a time. I can filter the data to just show the week I need but splitting out the number of times each item appears and formatting it for the pie chart is tedious.

I'll attach one that I have made as a sample of what I'm trying to automate.
Thank you for your insights
Mark

[geekgirlau]

What about this?

• Split the data out into a single column
• Have a preset pivot table based on that column, which is refreshed by the macro
• Have a preset chart based on the pivot table range

[mperrah]

I tried to modify the data to see changes oin the chart but cant figure it out. I am new to charts and pivot tables for that matter.
I changed the comment sectioin on my QC detail form to put an "x" under each column (26) and used a "counta" to total the column and make a chart.
It looks ok, but is there a way to pull the range of data from the qcdetail page based on the weekend or weekstart date? and update the chart?

MD helped my "wpr" page using weeknum() and sumproduct()
I read through the kb for these and am still learning the different ways to manipluate the data.

I'll post what I have so far.
Mark

[mperrah]

The data on the "chart" worksheet I copied and pasted.
The data on the wpr gets updated automaticaly from the qcdetail page based on the weeknum() formula.

can the weeknum() info update the chart data as well?

Mark

[geekgirlau]

In the Chart data, change the formula for the count to

=SUMPRODUCT((QCDetail!K$8:K$100="x")*(QCDate=WEEKNUM(WkStart))*1)

You won't need to copy the x's to the Chart sheet, as it will calculate directly from QCDetail.

[mperrah]

Thank you all!
I have a file that takes the raw data and automaticaly creates a chart around the date I type in, and my weekly report requires no typing! You rock.

I'll paste the end result for anyone to use.

Mark