"A formula in this worksheet contains one or more invalid references" for dynamic chart

[MrPez]

I'm getting the error above for a chart.

The chart has 2 series in it, one of which will always be empty. The chart itself seems to work ok.

The data I'm using is of the form:
AB
A
B
A*B (calculated in a cell)

Sometimes AB is known directly and can therefore be plotted directly. However, other times A and B are known individually. I then have another row to calculate the product of A and B.

So my two dynamic ranges are AB and A*B.

The problem is that when I know AB (and so A*B is empty) I get that stupid error when excel tries to plot the empty series?

The formula I'm using for A*B is of the form =IF(F18*F19/1000=0,"",F18*F19/1000)

Any help would be great!

 

[Andrew Poulsom]

Can you post some real values rather than letters please? In what way are your ranges dynamic?

 

[MrPez]

Ok, so here is some example data:

<tbody>
</tbody>

	01-08-14	02-08-14	03-08-14	04-08-14
AB	0.7	0.75	0.8	0.7
A	100	105	106	105
B	6	7	6	6
A*B/1000	0.6	0.735	0.636	0.63

<tbody>
</tbody>

I've put in here all values, but in practice either the second or third-fourth rows would be blank.

As more tests are done, additional columns will be added and my ranges update automatically to include the new data. The formula for the range is of the form =OFFSET(Master!$F$3,0,0,1,COUNTA(Master!$3:$3))

Hope this clarifies.
Cheers

 

[Andrew Poulsom]

What range does the data you posted occupy?

 

[MrPez]

I have the same situation on a few sheets, but say for this example it could be A1:E5

 

[Andrew Poulsom]

In that case I don't understand the dynamic formula.

 

[MrPez]

Sorry, I'm confusing things.
If the range is, say A1:E5 the named range formula for AB would be
=OFFSET(Master!$A$2,0,0,1,COUNTA(Master!$2:$2))
The AxB/1000 would be =OFFSET(Master!$A$5,0,0,1,COUNTA(Master!$5:$5))

 

[Andrew Poulsom]

I can't reproduce what you are seeing. I can clear the contents of B2:E4 without getting an error:

Excel 2010
	A	B	C	D	E
1		01/08/2014	02/08/2014	03/08/2014	04/08/2014
2	AB	0.7	0.75	0.8	0.7
3	A	100	105	106	105
4	B	6	7	6	6
5	A*B/1000	0.6	0.735	0.636	0.63
Master

Cell Formulas
Range	Formula
B5	=IF(B3*B4/1000=0,"",B3*B4/1000)

 

[MrPez]

Thanks anyway
I've tried deleting the chart and recreating it and still the same...
Never mind, I'll just have to come up with some other kind of chart.