• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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?
0
MrDavidThorn
Asked:
MrDavidThorn
1 Solution
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now