dynamic chart range based on certain conditions

**rishikrsaw** · 01-14-2015, 10:06 AM

Hi forum Members,

Pls help me I am try to create dynamic chart range based on certain conditions, in attached sheet tab "chart".

wherein I want if U4 = "total sat score" then should chart range ab5:ab11

else if U4 = "Weighted sat score" then should chart range ab5:ab10

In the tab "my try" I try to solve it by using VBA. however I could not succeed.

Pls let me know if it can done through VBA will be fine.

Thanks
Rishi

**Pauleyb** · 01-14-2015, 03:33 PM

Use dynamic ranges. For example, define a name chdata with this as its range definition: =OFFSET(chart!$AC$6,0,0,6+(chart!$U$4="total sat score"))

Then, select the chart's data source and edit the appropriate series. Change the 'Series values:' to =chart!chdata

Perform a similar task for the percentages and the x-axes.

**rishikrsaw** · 01-15-2015, 02:50 AM

Not working,

could you pls share the workbook if you got the result.....

thanks
Rishi

**Pauleyb** · 01-15-2015, 11:33 AM

Hmmm, not sure why its not working for you. I have attached my example (note: I removed the macros since I don't allow them to run on my system, but the chart concept works - just change the text in U4).

Another way to do this is to have your data in one section and then a 'smart data copy' in another section. The chart would use the 'smart data copy' and the data in it would be either a copy from the original chart or an NA. NA errors do not get charted. So, the equation where you want numbers to be included or not would be something like:
=IF(U4="total sat score",AC12,NA())