Is there any way to specify the 'Data Range' for a graph, using an IF formula (or otherwise), based on the value in another cell?
My graph data is contained within cells B17:P19. If the value in cell A1 = 'Region', then I only want my graph to plot the data within B17:F19, however, if the value in cell A1 changes to 'Area', I want to plot the data/replace the existing data with the values in G17:P19.
Is this possible and if so, how would I go about doing this?
It is possible! You need to create a dynamic named range, and then use this range as the data-input for your chart. You can find lots of threads here on this forum on how to do this, or you can upload a sample workbook and I (or someone else) will show you how.
Sincerely
S?ren Larsen
"Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"
Hi Søren,
I sort of understand how to make Dynamic Chart Ranges using the OFFSET Formula, which is how I assume I would need to do this, but I'm unsure how to link these to a specific cell.
I've attached a simple sample. What I would like to do is for the graph to plot using the data in cells B10:F12, when 'Region' appears in cell C4 and for the chart to use the data in cells G10:P12, when 'Area' appears in cell C4.
I'm unsure how to link cell C4 to the dynamic range and would be grateful for any help.
Many thanks
Unfortunately I misunderstood your problem. You want to dynamically change the number of series in the chart; correct? That you cannot do; you can only change the size of existing series.
So, I'm slightly confused. I could achieve this manually by simply selecting a different cell range for the chart data, which would allow the chart to update, is this not possible to do using formula, simply because there are different numbers of series in each set of data?
No, unfortunately you can't do this using formulas. If you change the chart area, you will see that the number of series are automatically changed. And if you input a named range, which is defined dynamically, this named range will automatically change to a "normal" range. So, if you want to achieve what you are looking for, then you need to do it using VBA.
If I were to adopt a VBA approach, do you know how this could work. I'm not accomplished with VBA, but would welcome any suggestions.
Many thanks
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
Last edited by Pete_UK; 06-20-2012 at 08:49 PM.
Please try the attached. It uses an event drive macro to recreate the chart when the value of the Area/Region cell is changed.
Code below.
Please Login or Register to view this content.
Martin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks