Automating a Chart w/ a row of criteria as Y axis

[Jennifer]

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?

 

[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

 

[dvt]

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

 

[crystall]

and where i can insert this function? thank you!

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

 

[crystall]

and where i can insert this function into? thank you!