Pie chart help please

[soph12081]

Hello

Please could somebody help me with a formula that enables a pie chart to update depending on the month.

So I have a pie chart divided into 8 sections but the values will change month by month so i'd like to know if its possible to get the pie chart to update when the cell V6 (month reference) changes.

Thanks in advance.

Sophie

 

[Gerald Higgins]

Hi - the best help I can give on pie charts is - consider using a different type of chart - they often suck.
See here for some reasons why
https://en.wikipedia.org/wiki/Pie_chart

But your problem sounds as if it's not specific to pie charts, it would apply equally to other types of chart that you might want to update monthly.

What you describe is definitely possible, but it's difficult to be precise as your request is too vague.

Can you give us more details please about what your data looks like ?

 

[soph12081]

Hello

Thanks for the reply.

So the data is

	April	May	June	July	August	September	October	November	December	January	February	March
Flint	34.23%	32.83%	32.66%	38.26%	49.48%	34.29%	37.84%	36.35%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Green	28.36%	28.58%	28.94%	28.00%	22.01%	31.13%	30.41%	24.17%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Amber	8.65%	8.67%	8.95%	7.76%	5.67%	8.24%	7.04%	10.76%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
CSP	14.65%	14.32%	16.67%	10.43%	10.08%	7.02%	8.63%	14.08%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Fines	11.08%	12.32%	9.64%	12.63%	10.46%	16.06%	12.90%	11.47%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Ferrous	0.36%	0.39%	0.37%	0.35%	0.27%	0.39%	0.38%	0.38%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Non-ferrous	0.51%	0.55%	0.52%	0.49%	0.39%	0.55%	0.53%	0.53%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Waste	2.16%	2.34%	2.23%	2.08%	1.64%	2.33%	2.26%	2.26%	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!

<colgroup><col><col span="2"><col span="2"><col span="3"><col><col span="4"></colgroup><tbody>
</tbody>

This table is sat in a tab called Reference2 and is cells B380:M387.

I have 5 other graphs on the sheet, the majority use a date range formula to change them, I have put in a formula at the minute that works but means that I have to change the reference every month to the correct month name rather than it update automatically.

Hopefully this makes sense?

Kind regards.

 

[Gerald Higgins]

Yes, the key to this is to use a helper column, which pulls in the relevant month's data, based on your selection, and then use that helper column as the source data for your chart, not your existing data range.

For example, let's say you use the range N380:N387 for your helper column.

Set up a cell somewhere to make your month choice (which could be controlled by data validation if you like).
Let's say this is cell N379.

Then use lookup formulas to lookup the month in N379, and pull in the relevant data from your source data, perhaps like this...
=HLOOKUP($N$379,$B$380:$M$387,2,false)
in cell N380.

Copy this all the way down to N387, and increment the 2 (in red above) by 1, for each row, so that in N381 you have
=HLOOKUP($N$379,$B$380:$M$387,3,false)
and so on.

Then use N380:N383 as the source data for your chart.

 

[soph12081]

Thank you SO much!

You don't understand how much I appreciate your help.

Thanks again.