Excel 2007 : Specify Chart Data Range using an IF Formula

Here's a formula approach which is "semi-automatic".

The graph derives its data from a common area which is large enough to cope with the larger ranges (coloured green). A simple IF statement determines where to get the data from to go into that common area, i.e. in G16:

=IF($C$4="Area",G10,C10)

I have arranged the data vertically so that it can be filtered more easily, so this formula is not just copied across or down. In the 5th row, the formula in G20 is this:

=IF($C$4="Area",K10,"")

which returns a blank if Region is selected in the drop-down in C4. This is slightly different in H20:

=IF($C$4="Area",K11,NA())

which returns the error #N/A if Region is selected, and the graph will ignore this.

So, the graph is set up to show the data by Area, but if you select Region in C4 the graph automatically shows only the 4 regions. The graph will not expand automatically, though - you will need to use the filter drop-down in cell I15 and unselect #N/A.If you then change C4 back to Area, though, you will have to use the filter drop-down to Select All - hence the reason for classing this as "semi-automatic".

Hope this helps.

Pete