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

MrPez

Board Regular
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!

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Can you post some real values rather than letters please? In what way are your ranges dynamic?

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

What range does the data you posted occupy?

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

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

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))

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

Excel 2010
ABCDE
101/08/201402/08/201403/08/201404/08/2014
2AB0.70.750.80.7
3A100105106105
4B6766
5A*B/10000.60.7350.6360.63
Master
Cell Formulas
RangeFormula
B5=IF(B3*B4/1000=0,"",B3*B4/1000)

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.

Replies
1
Views
700
Replies
1
Views
589
Replies
4
Views
2K
Replies
1
Views
423
Replies
0
Views
331

1,219,829
Messages
6,150,475
Members
450,967
Latest member
itzwinger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back