How to Give Upper & Lower Limits to a Plot?

[nick99]

I have a data range that changes dynamically.
To this Line plot i have to give upper and lower limits. what i mean is, these lines have to be parallel to the X axis.
The values of these upper and lower limits change on a regular basis.
I dont want to create another 2 columns to show this data on the plot.
is there a way where i can specify the values on the plot and excel draws these limits on the plot?

please refer to the attached sheet.

[daniel_d_n_r]

record a macro and change the upper and lower limits of the plot
you will find stuff like this..
then all you have to do is asign min and max to 2 variables
that you can add into somthing like below so excel reads the min and max automatically then changes the chart accordingly.

assign variables to what ever you want

Sub minmax()
Dim minimum, maximum As Long
minimum = Range("d3").Value
maximum = Range("d4").Value
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = minimum
.MaximumScale = maximum
End With
End Sub

select the whole columns, enter a min and max formula in the very end cells
protect the cells,(you will probably have to add protect/unprotect to the code).
use a function to access these values and assign then to variable when needed.

not sure if you can automate this fully but you could easily asign a shortcut key to it if all else fails.

if this is not exactly what you meant there is also many things you can access on an excel chart, record a macro and see if there is anything you can change

[JonPeltier]

Here's how to add lines manually:

http://peltiertech.com/Excel/Charts/AddLine.html

I dont want to create another 2 columns to show this data on the plot.

Worksheet columns are cheap, and they make life easier. If you don't want the user to see them, put them in another sheet somewhere. Or use a Name to determine the Y value for a line, and use this name as the source for the series that provides the line, as in a dynamic chart:

http://peltiertech.com/Excel/Charts/Dynamics.html