Home 
Search 
Today's Posts 
#1




charting noncontiguous data
Hi all,
Can someone tell me how to add noncontiguous data to a chart. My data is in B21, G21, L21........IR21. Ideally, the chart would autoupdate to include data as it is entered on a weekly basis. Thanks! 
#2




charting noncontiguous 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 noncontiguous data to a chart. My data is in B21, G21, L21........IR21. Ideally, the chart would autoupdate to include data as it is entered on a weekly basis. Thanks! 
#3




charting noncontiguous 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 noncontiguous data to a chart. My data is in B21, G21, L21........IR21. Ideally, the chart would autoupdate to include data as it is entered on a weekly basis. Thanks! 
#4




charting noncontiguous 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 noncontiguous data to a chart. My data is in B21, G21, L21........IR21. Ideally, the chart would autoupdate to include data as it is entered on a weekly basis. Thanks! 
#5




charting noncontiguous 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 noncontiguous data to a chart. My data is in B21, G21, L21........IR21. Ideally, the chart would autoupdate to include data as it is entered on a weekly basis. Thanks! 
#6




charting noncontiguous data
Hi,
A word of caution with this approach. Using the ctrl key to select discontiguous 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




charting noncontiguous 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 discontiguous 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




charting noncontiguous data
Hi Andy,
It used to be about 250 characters per series component (i.e., xvalues specs, yvalues 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 250whatever, 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 yvalues references.  Tushar Mehta http://www.tusharmehta.com Custom business solutions leveraging a multidisciplinary approach In Excel 2007 doubleclick 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 discontiguous 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




charting noncontiguous 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., xvalues specs, yvalues 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 250whatever, 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 yvalues references.  Tushar Mehta http://www.tusharmehta.com Custom business solutions leveraging a multidisciplinary approach In Excel 2007 doubleclick 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 discontiguous 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




charting noncontiguous 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., xvalues specs, yvalues 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 250whatever, 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 yvalues references. 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Charting Hidden Data  Charts and Charting in Excel  
sorting 2 colums of numbers and incremening them down  Excel Discussion (Misc queries)  
Macro question  Excel Worksheet Functions  
Excel Macro to Copy & Paste  Excel Worksheet Functions  
Pulling data from 1 sheet to another  Excel Worksheet Functions 