Constant Line on a Dynamic Chart Upper and Lower Limits

[canam]

I have seen similar topics addressed in the past but never on a dynamic chart.

I have 2 columns of data updated automatically every 20 seconds from a gage. These columns are then graphed on a dynamic line chart. What I want to do is add an upper and lower limit line to the same chart.

I currently have code that when a value is entered fills in the columns to the right with both limits and this is then charted. This works great until I get several thousand (10000) rows of data. The sheet becomes a little slow and the file size is huge. I would like to add these constant lines to the chart based on a formula or some other method that doesn't require the value to be entered next to every data cell.

Any help is greatly appreciated. Thanks

 

[Andrew Poulsom]

Look here:

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

You should be able to use formulas for the Min and Max.

 

[canam]

Andrew- Thanks for the respoonse. The solutions on that sight work great for a fixed chart, but don't change dynamically with rest of the chart. Any ideas?

 

[Andrew Poulsom]

How are you defining your dynamic range?

 

[canam]

Here's an example for one of the columns I am charting.

I'm not sure how to do it using any of the techniques described in your link.



=OFFSET(OD!$W$41,0,0,COUNT(OD!$W:$W),1)

 

[Andrew Poulsom]

Doesn't it work if you calculate the max and min on your dynamic named range?

 

[canam]

I couldn't get the error bar method to work. The line stays constant.

I don't understand your question?