Expanding my list for the chart....

**mark_sheffield** · 07-12-2010, 05:56 AM

Hi,

recently you have helped me with a particular spreadsheet (atatched). B1 only goes from Q1.1 to Q1.6. If I want to expand that how do I do it? I want to start to expand it to go to 2.1,2.2 etc. I can't seem to find where that list is configured.

Could someone please let me know?

Thank you.

Mark

**teylyn** · 07-12-2010, 06:30 AM

Hello Mark,

first, you need to extend the data validation in B1. It currently only offers
=$G$1:$L$1
as the data range. Extend it to
=$G$1:$N$1

Then, adjust your formulas in B2 to B7. Starting in B2

=INDEX(G2:N2,1,MATCH($B$1,$G$1:$N$1,0))

and copy down.

Every time you add a new column of Q.something, you'll need to update the formulas.

Alternatively, you could define dynamic range names that will grow with your data and reference the range names in your formulas.

May I suggest that you consider a different chart type? For one, pie charts should not really be used for more than two or three data points, because the human eye can not compare wedge sizes very well, and, secondly, the color for Excellent and NA are the same, so it's really hard to tell which is which.

cheers

**mark_sheffield** · 07-12-2010, 06:49 AM

Originally Posted by teylyn

Hello Mark,

first, you need to extend the data validation in B1. It currently only offers
=$G$1:$L$1
as the data range. Extend it to
=$G$1:$N$1

Then, adjust your formulas in B2 to B7. Starting in B2

=INDEX(G2:N2,1,MATCH($B$1,$G$1:$N$1,0))

and copy down.

Every time you add a new column of Q.something, you'll need to update the formulas.

Alternatively, you could define dynamic range names that will grow with your data and reference the range names in your formulas.

May I suggest that you consider a different chart type? For one, pie charts should not really be used for more than two or three data points, because the human eye can not compare wedge sizes very well, and, secondly, the color for Excellent and NA are the same, so it's really hard to tell which is which.

cheers

Thank you, that is great. I already knew about the colour and pie chart etc. That will all be tweaked once before I complete it.

Thanks