Dynamic series in Chart

Guys,
   I have a spreadsheet with timestamps on col A, then Col B contains the 
values correspond to timestamps in ColA (Col C thru Col M are similar to Col 
B as well).   I am trying to build a chart in function of time (meaning 
Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1 
series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so 
on...).
   Number of rows is subject to change (time stamp entries), and I am only 
interested for the last 21 entries (if there is less than 21 entries, plot 
them all, otherwise, only plot the last 21 entries).  For Instance, if there 
are 23 rows of data, the 1st series would be data on row3 Col B to Col M; the 
2nd series would be from Row4 ColB to Col M.. the 21st series would be from 
Row 23 ColB Col M.   If there are less that 21 rows, say, 5.  Then 1st series 
would be Row1 ColB to Col M; 2nd series would be Row2 ColB to ColM... 5th 
series = Row5 ColB..ColM.  
   Can someone please help me with the equation (offset?) in name definition?

Thanks
Jeff

0
Jeff1 (635)
2/23/2005 5:23:09 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
373 Views

Similar Articles

[PageSpeed] 37

use something like these stataements

Dim lastrow As Range
Dim myrange As Range
Set lastrow = Range("a60000").End(xlUp)
Set myrange = Range(lastrow, lastrow.Offset(-20, 0))
MsgBox myrange.Address

you will get last 21 rows in column A


Jeff <Jeff@discussions.microsoft.com> wrote in message
news:6E715F66-98C3-4840-BF15-949B3AB7EC5F@microsoft.com...
> Guys,
>    I have a spreadsheet with timestamps on col A, then Col B contains the
> values correspond to timestamps in ColA (Col C thru Col M are similar to
Col
> B as well).   I am trying to build a chart in function of time (meaning
> Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the
1
> series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so
> on...).
>    Number of rows is subject to change (time stamp entries), and I am only
> interested for the last 21 entries (if there is less than 21 entries, plot
> them all, otherwise, only plot the last 21 entries).  For Instance, if
there
> are 23 rows of data, the 1st series would be data on row3 Col B to Col M;
the
> 2nd series would be from Row4 ColB to Col M.. the 21st series would be
from
> Row 23 ColB Col M.   If there are less that 21 rows, say, 5.  Then 1st
series
> would be Row1 ColB to Col M; 2nd series would be Row2 ColB to ColM... 5th
> series = Row5 ColB..ColM.
>    Can someone please help me with the equation (offset?) in name
definition?
>
> Thanks
> Jeff
>


0
R
2/24/2005 2:53:05 AM
I'm not sure I understand what you want to do and the examples in the 
two paragraphs do not appear to be consistent.

In the first para, it appears that the x-values of a particular series 
are the same as the y-values of the previous series.  Is that correct?  
And, in any case it appears that the first series x-values are in row 
2.

However, in para 2, with only 5 rows of data you refer to row 1 as the 
source for the first series.  Where are the x-values?

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <6E715F66-98C3-4840-BF15-949B3AB7EC5F@microsoft.com>, 
Jeff@discussions.microsoft.com says...
> Guys,
>    I have a spreadsheet with timestamps on col A, then Col B contains the 
> values correspond to timestamps in ColA (Col C thru Col M are similar to Col 
> B as well).   I am trying to build a chart in function of time (meaning 
> Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1 
> series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so 
> on...).
>    Number of rows is subject to change (time stamp entries), and I am only 
> interested for the last 21 entries (if there is less than 21 entries, plot 
> them all, otherwise, only plot the last 21 entries).  For Instance, if there 
> are 23 rows of data, the 1st series would be data on row3 Col B to Col M; the 
> 2nd series would be from Row4 ColB to Col M.. the 21st series would be from 
> Row 23 ColB Col M.   If there are less that 21 rows, say, 5.  Then 1st series 
> would be Row1 ColB to Col M; 2nd series would be Row2 ColB to ColM... 5th 
> series = Row5 ColB..ColM.  
>    Can someone please help me with the equation (offset?) in name definition?
> 
> Thanks
> Jeff
> 
> 
0
2/24/2005 11:54:09 AM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...

How to generate dynamic XML from aspx page
Dear Friends, I was using the below code in ASP to dynamically generate XML from a SQL Query Does anyone knows how can I migrate this code to VB and ASPX? Regards Robson Machado Response.ContentType = "text/xml" Response.Write "<?xml version='1.0' ?>" dim RS, CN set CN = server.CreateObject("adodb.connection") set RS = server.CreateObject("adodb.recordset") CN.ConnectionString = strConnect CN.Open RS.Open SQLStmt,cn Response.Write "<ROOT>" Response.Write RS2XML(RS,"RESULT") Response.Write "</R...

"Dynamic message" on a report
I have been trying to figure out a way to do this and have not been able to find anything concrete to help me. I have a report containing several sub-reports. It is an inventory report for a variety of production lines. If the inventory on a certain die set is less then 0.5, I want to be able to display a message on the report telling the user that the inventory on this die set is low drawing attention to it. Can anyone provide an example of how I can accomplish this? I was thinking of an If, Then, Else If but have never seen it for a report. Any assistance would be appreciated. Thank yo...

chart type keeps changing
My chart is "embedded" in my worksheet. The worksheet has months across the top in one row and net income per share in a row below it for each corresponding month. The x-axis is pulling data for the month and year eg. Jan-04...Feb-06. The y-axis is pulling data from a different row for the net income for each month. The chart has been maintained since Jan-04. It is a standard clustered column w/ a 3-D visual effect. I am trying to enter data for March '06 in data source, series one. I am updating the series 1 with the new column address that represents the new month eg...

Dynamic Bar Chart Category Axis Problem
Hi I have created a dynamic bar chart, roughly based on Stephen Bullen's funchrt4. The number of categories can range from 2 to about 50 and ideally I would like the category axis names font to be Arial 10 at least. When the number of categories gets towards 50, the category axis names disappear altogether and the only way I have found to retrieve them is to manually reduce the font size to about 7 (dependent on the level of the zoom). This font size is excessively small for the majority of charts. Is there any way to get Excel2002 to keep the category axis names in all circumstan...

MS chart control for VC++9 03-13-10
I am trying to use MSchart control in my Visual C++9, However, the MS chart control in not available in activex list!. I ve downloaded the MS chart control for APS. Net but I couldnt use it with my VC++ programs. Is there any clue? thanks On Fri, 12 Mar 2010 17:50:52 -0800, Azed <user@msgroups.net/> wrote: >I am trying to use MSchart control in my Visual C++9, However, the MS chart control in not available in activex list!. I ve downloaded the MS chart control for APS. Net but I couldnt use it with my VC++ programs. >Is there any clue? >thanks > >--- >frmsrcur...

Chart of Accounts Details
Can you use alphanumerics or just numerics when defining a chart of account id? You can use an Alphanumeric chart but proceed carefully - it often proved to be impractical and rigid. HS "Royden Akerley" <royden.akerley@brascanpower.com> wrote in message news:225d201c45d4c$6f57a8d0$a301280a@phx.gbl... > Can you use alphanumerics or just numerics when defining a > chart of account id? Some auditors have problems with alpha account numbers. Proceed with caution. "Royden Akerley" <royden.akerley@brascanpower.com> wrote in message news:225d201c45d4c$6f5...

Display value on a form dynamically
I have a form that needs to display values from an array on the form; how do I achieve the following? If item count in array is < 20 then print Val1 Val2 Val3 Val4 Val5 Val6 and so on If items in array is > 20 then print Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 and so on The following code works fine and prints values in two columns butI need to modified it to print in three columns if nItemsCount > 20 { for (i = 1; i <= nItemsCount; i++) { if (i % 2) { nX = nX * 25; ...

Pivot Tables and Graphs (charts)
Background: I have a Pivot Table (PT) which is updated monthly. A chart is tied to the data in the PT. The X-axis contains the months and the Y-axis contains the respective data of the months. Question: How can the chart be updated automatically (this should include the latest monthly data) whenever the PT is updated (refreshed)? Thanks in advance. learnlearn52 wrote: > Background: > > I have a Pivot Table (PT) which is updated monthly. A chart is tied to the > data in the PT. The X-axis contains the months and the Y-axis contains the > respective data of the months....

Dynamic Excel Charts in Word
Can I link data from columns in Excel to a pie chart in a mail merged Word document so the pie chart changes as I scroll through the entries? If this is possible, how do I do it? Any help would be appreciated, especially if my efforts are in vain. Thanks. ...

multiple charts on one sheet
I had three charts on a sheet and wanted them on one sheet. I used the method on J-walk site but as I am using 2007 I am unable to locate View. sized with window command, to stack the charts and space them any idea will be greatly appreciated. AFD Hi, Not clear on what you want. To position charts on a worksheet I hold down the Alt key as I drag and resize the charts, this causes them to lock to grid. This worked in 2003 and still does in 2007. If you are asking where is the View, Chart Window command is in 2007 - its gone. -- If this helps, please click the Yes button. Cheer...

Chart to Word
I have a flow chart in Excell. It is quite wide. I have been copying the whole thing and pasting it special, as a picture in word. But I only get 3/4 of the chart in Word. I have checked and re-copied it many times to make sure I am picking up the whole chart and I have. But when I go to copy it I only get 3/4 of the chart. Can anyone tell me what to do to overcome this problem??? aurora have you tried to resize the chart in Word. What size does it say it is? "Aurora" wrote: > I have a flow chart in Excell. > It is quite wide. I have been copying the whole thing ...

HOW TO FIX SIZE OF CHART BOX
I have many charts and their size (width and length) is not same. Is there a command to give/specify the size of the chart box? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan ...

Excel Chart colors help
How do I create the faded color effect like the tornado chart example in this link: http://peltiertech.com/Excel/Charts/tornadochart.html It's the orange and purple chart and the bottom of the page Thanks, Paula -- paulabrozek ------------------------------------------------------------------------ paulabrozek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30377 View this thread: http://www.excelforum.com/showthread.php?threadid=552751 Hi, Double click one of the sets of bars. On the Pattern tab of the format dialog click Fill Effects. On the Gradi...

Update Chart Titles with Worksheet_Change Event
I have a chart sheet called ("Histogram")with an active X dropdown box which pulls in data from Sheet1. The Active X dropdown list sends a number to a named cell ("ChartNumber") on Sheet1 which is then used within 2 Index formulas to create an active range for my X and Y range to feed my chartsheet("Histogram"). I am trying to update my chart X axis title and my X axis format using a Worksheet_Change Event within Sheet1. I have four basic titles and formats which I have put inside a Case Statement. However I cannot seem to make it work. I want the Worksheet_C...

Negative Values in Stacked Bar Charts
I'd like to include negative values in stacked bar charts, with the total stacked value equal to the sum of each of the subtotals. However, when I tested this, it appears that Excel just shows the negative components at the bottom of the bar, below zero, but the stacked total still seems to reflect the sum of the positive components. Is there a way of showing a stacked bar the way I'm trying to do it? Todd Hi, In order to plot the chart how you want you would have to make the negative values positive. You may need to explain further why you need a negative value should be...