View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.charting
Andy Pope Andy Pope is offline
external usenet poster
 
Posts: 2,489
Default charting non-contiguous data

Hi Tushar,

As Jon has posted the point of entry is key to passing the 256 limit.
BUT I have just had a play. I used the formula bar to extend the chart
series, I realise the cells choose are in fact contiguous I was being lazy.

=SERIES(,,(Sheet2!$A$1,Sheet2!$A$2,Sheet2!$A$3,She et2!$A$4,Sheet2!$A$5,Sheet2!$A$6,
Sheet2!$A$7,Sheet2!$A$8,Sheet2!$A$9,Sheet2!$A$10,S heet2!$A$11,Sheet2!$A$12,Sheet2!$A$13,
Sheet2!$A$14,Sheet2!$A$15,Sheet2!$A$16,Sheet2!$A$1 7,Sheet2!$A$18,Sheet2!$A$19,Sheet2!$A$20),1)

When you open the Source Data dialog AND the Data Range tab is active it
reports a formula error as it truncates the formula!

Building the non contiguous range using Named ranges seems to work.

ChtD1:
=(Sheet2!$A$1,Sheet2!$A$2,Sheet2!$A$3,Sheet2!$A$4, Sheet2!$A$5,Sheet2!$A$6,Sheet2!$A$7,Sheet2!$A$8,Sh eet2!$A$9,Sheet2!$A$10,Sheet2!$A$11,Sheet2!$A$12)

ChtD2 and ChtD3 are the same but for columns B and C

Series formula is
=SERIES(,,(Book1!ChtD1,Book1!CHtD2,Book1!CHtD3),1)

And this does not display in the Data Range section of the dialog.

If you take this a step further and create a named range that joins the
other named ranges,
AllData:
=(ChtD1,CHtD2,CHtD3)

You can cut the series formula back to,
=SERIES(,,Book1!AllData,1)

and still get all the points.

Cheers
Andy

Tushar Mehta wrote:
Hi Andy,
It used to be about 250 characters per series component (i.e., x-values
specs, y-values specs etc.). Maybe those individual limits got lifted at
some point but they seem to be back w/2007. I just tried plotting a simple
clustered column chart and XL balked at about 256. Of course, the way it
complained was bizarre.

When creating a chart (changed the worksheet name to the longest allowed,
entered =ROW() in a column and CTRL+picked every alternate cell), the error
message I got was "some types of charts cannot be combined with others."
Don't ask why.

When trying to extend a series formula, nothing happens. Edited the current
formula by entering a comma before the closing parentheis and CTRL+clicked to
add new cells. If the resulting length was 250-whatever, pressing ENTER
did nothing. No error message, no nothing. It showed the updated formula in
the formula bar but it wouldn't accept it nor provide any kind of error
message. You just had to know that the problem was the length of the
y-values references.