Solved

Excel Dynamic Chart series

Posted on 2012-04-11
1
265 Views
Last Modified: 2012-06-27
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
Comment
Question by:MrDavidThorn
1 Comment
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37834237
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now