maximum for noncontiguous cells in chart?

  • Thread starter Thread starter doug86
  • Start date Start date
D

doug86

Hi, I'm new to this forum and new to Excel. I'm on Mac 10.3.9 using
Excel 2004.

I'm making a chart by selecting 12 cells (one for each month) which use
formulas (simple sums) and are not next to each other. I can get up to
ten of them to go into a new chart using the chart wizard button. But
if I select all twelve I get the dreaded message, "Your formula
contains an invalid external reference to a worksheet."

I have no idea what that means.

I've tried manually typing in the data range of the edit box for the
chart, but what I type literally won't show up in the data range entry
line beyond a certain point. Wierd.

I'd appreciate any ideas from you more experienced Excel folks.

Thanks.
 
Hi,

Not an owner of a Mac but I think the problem is also valid on a PC.
If you select a data series within a chart the series formula is
displayed in the formula bar. This formula has a length limit of 1024
characters, at least I think thats the limit.
Try reducing the length of the sheetname. If that is not possible the
other alternative is to create a consolidated range just for the purpose
of the chart data.

Also this information posted by Jon Peltier may help explain chart
series formula.
http://peltiertech.com/Excel/ChartsHowTo/ChartSeriesFormula.html

Cheers
Andy
 
The formula limit is 1024, but each component of the SERIES formula (name, X
values, Y values, and plot order) are allotted equal portions of this. So
the real limit is around 250 characters (less than 256 because of commas and
parentheses). What's worse is that each cell's reference must include the
sheet name, so the longer the sheet name, the fewer cells you can include.

The best thing to do is arrange your data appropriately, so you can select a
contiguous range for the chart source data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
You guys are great. Thanks for the quick help.

IMHO this sounds like a design flaw of Excel. But now that you gave me
the work around I fixed it. It's nice to know I wasn't missing
something really obvious.

Thanks again.
 
Back
Top