Change chart source dependant on cell contents

**jwillis07** · 08-12-2014, 07:18 AM

Hi all,

Is it possible to change the source range for a chart dependant on the contents of a cell.

Example worksheet attached!

i.e...

If C1 = "Yes", then use the cell range A3:C10, if not use cell range A3:B10.

Many thanks in advance

Jason

**ragulduy** · 08-12-2014, 08:42 AM

I would normally use named ranges to do this.

Create a named range using the name manager and in the box for "refers to" enter the formula:
=IF(Sheet1!$C$1="Yes",Sheet1!$C$4:$C$10,Sheet1!$B$4:$B$10)
then give it a suitable name, for example chart_data

Then in the source data for your chart enter the named range (you will need a sheet reference as well, for example Sheet1!chart_data

**Andy Pope** · 08-13-2014, 06:36 AM

If you use named range for the Source data reference the chart will be populated correctly but the series formula will be range references.

This means when C1 changes the chart will not.

You can use the named range approach but you need to define category and value ranges for each series.