Hi all,

I am currently making a chart that needs to take its data from 1 of 2
series', depending on whether cell A1 contains the number 1 or 2. I tried to
use the IF function to do this "=IF(A1=1, Range 1, Range 2)", but it just
keeps telling me i cant use the IF function in a chart. I cannot find a way
to achieve this, and its pretty critical i can, otherwise i'm going to have
to start a weeks worth of work again!

Thanks
Edd

Hi Edd,

Unfortunately, you cannot use the IF function directly in a chart to choose its data series. However, there is a workaround that you can use to achieve the desired result.

Here are the steps to follow:

1. Create both data series in your worksheet, one for when A1 equals 1 and the other for when A1 equals 2.
2. Create a third column that uses the IF function to determine which data series to use based on the value in A1. For example, if your data series are in columns B and C, your formula in column D could be "=
   Code:

   IF(A1=1,B1:C1)

   ".
3. Copy the formula in column D down to all the rows that contain data.
4. Create your chart using the data in column D as the data source.

By using this workaround, your chart will dynamically choose the correct data series based on the value in A1. If A1 changes, the chart will automatically update to reflect the new data series.

Hi,

The series data references can not handle formulas directly, only range
references. In order to make the chart dynamic you can either create
another set of cells that contain your IF() formula and then chart those
cells. Or use named ranges that are dynamic. For more information on
those see these explanations.

http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/ne...rts/index.html

Cheers
Andy

Edd UK wrote:
Hi all,

I am currently making a chart that needs to take its data from 1 of 2
series', depending on whether cell A1 contains the number 1 or 2. I tried to
use the IF function to do this "=IF(A1=1, Range 1, Range 2)", but it just
keeps telling me i cant use the IF function in a chart. I cannot find a way
to achieve this, and its pretty critical i can, otherwise i'm going to have
to start a weeks worth of work again!

Thanks
Edd

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info