If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
Hi, I hope someone can help. I can automate a chart given 1 x- and y-
axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? |
#2
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
Jennifer -
It sounds like this means changing the length and width of the source data range. Unfortunately, you can only change the length of a series using worksheet functions and defined names, but you can't change the number of series in a chart. In a contour/surface type of chart, the three axes are called Category, Series, and Value, and you can only change the number of Categories. It's easy enough to define the range that covers the source data range. But you either need to manually apply it to the chart, or use a VBA routine, triggered by a button or by a change in the data. Say you have a dynamic range name "ChartData" and only one chart on the worksheet. This procedure will update the chart when data in the source data range changes: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("ChartData")) Is Nothing Then ChartObjects(1).Chart.SetSourceData Source:=Range("ChartData") End If End Sub Right click on the worksheet tab, select View Code, and paste this into the code window that pops up. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jennifer wrote: Hi, I hope someone can help. I can automate a chart given 1 x- and y- axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? |
#3
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
Jennifer wrote:
Hi, I hope someone can help. I can automate a chart given 1 x- and y- axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? I don't really understand how this is different than a dynamic 2D chart. For example, if your data is in columns A:E on Sheet2, you could name the data for one series as February and use this formula: =OFFSET(Sheet2!$C$2,0,0,COUNT(Sheet2!C:C)) Now if you add a new week, the chart should update properly. What doesn't work for you? Dave dvt at psu dot edu |
#4
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
hi,i also have the same problem, i want to give your a excel file, and
would you mind to show it to me in this file? thank you ver much. "Jon Peltier" ???? ... Jennifer - It sounds like this means changing the length and width of the source data range. Unfortunately, you can only change the length of a series using worksheet functions and defined names, but you can't change the number of series in a chart. In a contour/surface type of chart, the three axes are called Category, Series, and Value, and you can only change the number of Categories. It's easy enough to define the range that covers the source data range. But you either need to manually apply it to the chart, or use a VBA routine, triggered by a button or by a change in the data. Say you have a dynamic range name "ChartData" and only one chart on the worksheet. This procedure will update the chart when data in the source data range changes: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("ChartData")) Is Nothing Then ChartObjects(1).Chart.SetSourceData Source:=Range("ChartData") End If End Sub Right click on the worksheet tab, select View Code, and paste this into the code window that pops up. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jennifer wrote: Hi, I hope someone can help. I can automate a chart given 1 x- and y- axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? |
#5
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
and where i can insert this function? thank you!
"dvt" дÈëÓʼþ ... Jennifer wrote: Hi, I hope someone can help. I can automate a chart given 1 x- and y- axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? I don't really understand how this is different than a dynamic 2D chart. For example, if your data is in columns A:E on Sheet2, you could name the data for one series as February and use this formula: =OFFSET(Sheet2!$C$2,0,0,COUNT(Sheet2!C:C)) Now if you add a new week, the chart should update properly. What doesn't work for you? Dave dvt at psu dot edu |
#6
|
|||
|
|||
Automating a Chart w/ a row of criteria as Y axis
and where i can insert this function into? thank you!
"dvt" дÈëÓʼþ ... Jennifer wrote: Hi, I hope someone can help. I can automate a chart given 1 x- and y- axis. However when I try to apply the same offset principle to a chart w/ an x-axis consisting of more than 1 criteria, it won't work. For example, my table looks like the following: Week # January FebruaryMarch April week 48 10000 20000 30000 40000 week 49 10000 20000 30000 40000 week 50 10000 20000 30000 40000 week 51 10000 20000 30000 40000 week 52 10000 20000 30000 40000 week 53 10000 20000 30000 40000 week 54 10000 20000 30000 40000 My chart is 3-dimensional, week # is the z axis, months the x axis, and the sales the y axis. I am trying to use the offset function to automate the chart to update when a new week number is added. Any ideas? I don't really understand how this is different than a dynamic 2D chart. For example, if your data is in columns A:E on Sheet2, you could name the data for one series as February and use this formula: =OFFSET(Sheet2!$C$2,0,0,COUNT(Sheet2!C:C)) Now if you add a new week, the chart should update properly. What doesn't work for you? Dave dvt at psu dot edu |
Thread Tools | |
Display Modes | |
|
|