Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 6th 07, 12:16 PM posted to microsoft.public.excel.charting
tom tom is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 570
Default charting non-contiguous data

Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!

  #2   Report Post  
Old February 6th 07, 02:02 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default charting non-contiguous data

Running out of weeks, since the last day goes into IV21.

Ideally, you would set up a contiguous chart data range in another sheet, or
in another part of this sheet, which links to the discontiguous data. The
problem is that the individual cells of your chart data are stored as a
sheet name and cell address in the series formula, taking a dozen or more
characters per cell, and you only get around 250 characters for X and 250
for Y.

You can easily use index or offset formulas to extract the data you need:

In cell B41, for example, use one of these:

=OFFSET($B$21,0,(COLUMN()-2)*5)
=INDEX($B$21:$IR$21,(COLUMN()-2)*5+1)

and fill across to BA41.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



"Tom" wrote in message
...
Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is
in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!



  #3   Report Post  
Old February 6th 07, 02:12 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default charting non-contiguous data

If I had this problem I would contrive to collect the data to be plotted in
a contiguous range.
Suppose your data is in Sheet1 B21:IR21
On another sheet in A1 enter =INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5)
Copy this AY1
Now plot this data - the chart can be anywhere you like - it need not be on
the sheet with the data.
To make the chart dynamic change the formula to
=IF(INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),NA())
and plot the whole range A1:AY1. When cells on Sheet1 are blank, the data to
plot will display #N/A and this will be ignore by the chart engine.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is
in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!



  #4   Report Post  
Old February 6th 07, 02:18 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 51
Default charting non-contiguous data

You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then either
click & drag it to the chart or use copy & paste
--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Tom" wrote:

Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!

  #5   Report Post  
Old February 6th 07, 03:01 PM posted to microsoft.public.excel.charting
tom tom is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 570
Default charting non-contiguous data

Works perfectly....thanks so much!

"Bernard Liengme" wrote:

If I had this problem I would contrive to collect the data to be plotted in
a contiguous range.
Suppose your data is in Sheet1 B21:IR21
On another sheet in A1 enter =INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5)
Copy this AY1
Now plot this data - the chart can be anywhere you like - it need not be on
the sheet with the data.
To make the chart dynamic change the formula to
=IF(INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),NA())
and plot the whole range A1:AY1. When cells on Sheet1 are blank, the data to
plot will display #N/A and this will be ignore by the chart engine.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is
in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!






  #6   Report Post  
Old February 6th 07, 03:26 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,489
Default charting non-contiguous data

Hi,

A word of caution with this approach.

Using the ctrl key to select dis-contiguous cells will work but only to
a point. Once the length of the series formula exceeds 1024, I think
that's the cutoff length, you will not be able to add anymore points to
that series. When you consider each point requires a sheet and cell
reference the formula can get very long very quickly.

Cheers
Andy

john the confused wrote:
You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then either
click & drag it to the chart or use copy & paste


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #7   Report Post  
Old February 6th 07, 04:38 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 51
Default charting non-contiguous data

Point taken - I forgot about the limits.
You still gives you upto about 80 data cells to a series though.
--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Andy Pope" wrote:

Hi,

A word of caution with this approach.

Using the ctrl key to select dis-contiguous cells will work but only to
a point. Once the length of the series formula exceeds 1024, I think
that's the cutoff length, you will not be able to add anymore points to
that series. When you consider each point requires a sheet and cell
reference the formula can get very long very quickly.

Cheers
Andy

john the confused wrote:
You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then either
click & drag it to the chart or use copy & paste


--

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

  #8   Report Post  
Old February 7th 07, 05:57 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,071
Default charting non-contiguous data

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

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Andy Pope" wrote:

Hi,

A word of caution with this approach.

Using the ctrl key to select dis-contiguous cells will work but only to
a point. Once the length of the series formula exceeds 1024, I think
that's the cutoff length, you will not be able to add anymore points to
that series. When you consider each point requires a sheet and cell
reference the formula can get very long very quickly.

Cheers
Andy

john the confused wrote:
You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then either
click & drag it to the chart or use copy & paste


--

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

  #9   Report Post  
Old February 7th 07, 07:16 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default charting non-contiguous data

I did a little playing in Excel 2003. If you use the Source Data Series
dialog, you cannot enter more than about 256 characters into the X Values or
Y Values box. If you directly edit the series formula, it seems you can use
nearly all of the 1024 characters for the Y Values, provided the X Values
argument is omitted. This last surprised me, because I've thought for a long
time that the overall series length didn't matter, but only the length of
the X and Y components of the formula. It may be that earlier Excels had
this limit, and by 2003 the limit was removed, or it may be that the limit
still exists in VBA, which is where I've spent more time trying to get
around it. Certainly VBA can't do as much with a chart series as a user can
working within the user interface.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tushar Mehta" wrote in message
...
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.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Andy Pope" wrote:

Hi,

A word of caution with this approach.

Using the ctrl key to select dis-contiguous cells will work but only to
a point. Once the length of the series formula exceeds 1024, I think
that's the cutoff length, you will not be able to add anymore points to
that series. When you consider each point requires a sheet and cell
reference the formula can get very long very quickly.

Cheers
Andy

john the confused wrote:
You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then
either
click & drag it to the chart or use copy & paste


--

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



  #10   Report Post  
Old February 7th 07, 08:58 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Charting Hidden Data big t Charts and Charting in Excel 3 November 8th 06 04:34 PM
sorting 2 colums of numbers and incremening them down blk&wht Excel Discussion (Misc queries) 10 October 9th 06 10:12 PM
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 08:12 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017