Excel Dynamic Chart series

Hi Experts

I have a chart in Excel- one series formula is

=SERIES(BollingerBands!$A$1,,BollingerBands!$A$2:$A$500,3)

However the range A2:A500 populated values can change  - So sometimes I just need A2:A300 for instance

I have a formula to return the last cell address in the range A1:A500 that I want to use, but I dont know how to refernce that formula in the series - in this example in B1

something like =SERIES(BollingerBands!$A$1,B1,,3) - doesnt work, any ideas?
MrDavidThornAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dlmilleConnect With a Mentor Commented:
Here's a tip on creating charts from dynamic ranges:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

While a formula could be embedded in the chart, I think the best way is to create a named range (e.g., myRangeValues) that represents your dynamic formula for a series dimension - e.g., to derive BollingerBands!$A$2:$A$500

Then, you should be able to use:

=SERIES(BollingerBands!$A$1,,BollingerBands!myRangeValues,3)

See attached.

Dave
dynamicChart-r2.xls
0
All Courses

From novice to tech pro — start learning today.