Thread: maximum for noncontiguous cells in chart?

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/ChartsH...esFormula.html

Cheers
Andy

doug86 wrote:
> 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.
>
>

--

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

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/
_______


"Andy Pope" <andy@andypope.info> wrote in message
news:uhtSeG0GGHA.532@TK2MSFTNGP15.phx.gbl...
> 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/ChartsH...esFormula.html
>
> Cheers
> Andy
>
> doug86 wrote:
>> 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.
>>
>>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info

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.