Variable time scaling for different views in a graph

[Guest]

Hi,
I need to know, if it is possible to set the x-Axis of a graph with variable
values.

Example:
I have a projekt running over 3 Years. I would like to have 2 views. First
one is over the complete project duration, second one is only the current
year.

As far as I know, I can only set the scale automatically or manual. In
manual mode the is no variable value or cell exepted.

Is there any idea out there to help me out?
Thanks, Matz

 

[SIR Knight]

Matz.

If you plot the graph for the full period, you can then hide the
columns on the worksheet which you do not wish to show in the graph.
All of the hidden data, including labels on the axes will not show
while the columns are hidden

thanks

Steve

 

[Guest]

Hi Stieve
thanks for the fast response - but it is a bit mor complicate.

The table contains Date values (dates for a time schedule).

Normally, I have the scale set from minimum Date to maximum Date - typically
3 years.
Now I just need one period out of the range (typically current year)

Table:
A B C D
1 Date1 Date2 Date3 Date4
2 Date5 Date6 Date7 Date8
3 ....
each column respresents one serie.

Hiding colums would not work because I would loose my data points.
Any idea?
Thanks!
Matz

 

[SIR Knight]

Matz,

You can do this using VBA as follows

If you enter your start date in Cell A1, and End Date in A2

you can then use a macro to use these values in the scale:

'set start and end dates
uvStart = Format(Range("A1").Value, "##")
uvEnd = Format(Range("A2").Value, "##")

'set Axes values to styart and end dates
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = uvStart
.MaximumScale = uvEnd
End With

I have assumed that your chart is called "Chart 1". You can change the
code as required for chart name or location of you dates

I hope this helps

Steve

 

[Guest]

Great help! Thanks!
After I have found out how to write a Macro in VBA it worked in principle.
Now I have to do some details.
Happy so far Matz

 

[Jon Peltier]

You can also do this without VBA using dynamic ranges:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

- Jon