# 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?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by