software

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, quot;Your formula
contains an invalid external reference to a worksheet.quot;

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.--
doug86
------------------------------------------------------------------------
doug86's Profile: www.excelforum.com/member.php...oamp;userid=30540
View this thread: www.excelforum.com/showthread...hreadid=501945Hi,

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

Cheers
Andy

doug86 wrote:
gt; Hi, I'm new to this forum and new to Excel. I'm on Mac 10.3.9 using
gt; Excel 2004.
gt;
gt; I'm making a chart by selecting 12 cells (one for each month) which use
gt; formulas (simple sums) and are not next to each other. I can get up to
gt; ten of them to go into a new chart using the chart wizard button. But
gt; if I select all twelve I get the dreaded message, quot;Your formula
gt; contains an invalid external reference to a worksheet.quot;
gt;
gt; I have no idea what that means.
gt;
gt; I've tried manually typing in the data range of the edit box for the
gt; chart, but what I type literally won't show up in the data range entry
gt; line beyond a certain point. Wierd.
gt;
gt; I'd appreciate any ideas from you more experienced Excel folks.
gt;
gt; Thanks.
gt;
gt;

--

Andy Pope, Microsoft MVP - Excel
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
PeltierTech.com/
_______quot;Andy Popequot; gt; wrote in message
...
gt; Hi,
gt;
gt; Not an owner of a Mac but I think the problem is also valid on a PC.
gt; If you select a data series within a chart the series formula is displayed
gt; in the formula bar. This formula has a length limit of 1024 characters, at
gt; least I think thats the limit.
gt; Try reducing the length of the sheetname. If that is not possible the
gt; other alternative is to create a consolidated range just for the purpose
gt; of the chart data.
gt;
gt; Also this information posted by Jon Peltier may help explain chart series
gt; formula.
gt; peltiertech.com/Excel/ChartsH...esFormula.html
gt;
gt; Cheers
gt; Andy
gt;
gt; doug86 wrote:
gt;gt; Hi, I'm new to this forum and new to Excel. I'm on Mac 10.3.9 using
gt;gt; Excel 2004.
gt;gt;
gt;gt; I'm making a chart by selecting 12 cells (one for each month) which use
gt;gt; formulas (simple sums) and are not next to each other. I can get up to
gt;gt; ten of them to go into a new chart using the chart wizard button. But
gt;gt; if I select all twelve I get the dreaded message, quot;Your formula
gt;gt; contains an invalid external reference to a worksheet.quot;
gt;gt;
gt;gt; I have no idea what that means.
gt;gt;
gt;gt; I've tried manually typing in the data range of the edit box for the
gt;gt; chart, but what I type literally won't show up in the data range entry
gt;gt; line beyond a certain point. Wierd.
gt;gt;
gt;gt; I'd appreciate any ideas from you more experienced Excel folks.
gt;gt;
gt;gt; Thanks.
gt;gt;
gt;gt;
gt;
gt; --
gt;
gt; Andy Pope, Microsoft MVP - Excel
gt; 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.--
doug86
------------------------------------------------------------------------
doug86's Profile: www.excelforum.com/member.php...oamp;userid=30540
View this thread: www.excelforum.com/showthread...hreadid=501945