Solved

Base Excel chart on an Excel table to dynamically update chart

Posted on 2012-12-28
6
144 Views
Last Modified: 2013-04-10
Hello, I have an Excel combination chart already built and rockin'!!

My dilemma at the moment is in how to dynamically update the Excel charts (I have a different Excel chart for every County in their own respective tabs) based on the data in a different tab.

That tab being referenced is where I am storing all of the membership counts and calculated percentages of the membership counts for each category. For example, I have Fresno (region), Family (aid category), medi-cal (coverage type) listed as one of the rows where the column headings are all of the month by month percentages. These percentages are just calculations of the membership counts for each of the same categories (not sure all of that really matters for the question I am asking, but thought it might help).

So I did a little research and found 'Base the chart on an Excel table' at this link: http://office.microsoft.com/en-us/excel-help/update-the-data-in-an-existing-chart-HP010342980.aspx but it doesn't work the way the short instructions say it will work??

If I click the chart and go to Select Data in Design tab, then click on the Chart Data Range button on right and then point to my tab that has all of the percentages and go to Insert tab, the Table button is grayed out (won't allow me to select it)??

I have tried to just highlight the whole cell range and insert Table by itself (not going through Select Data for the chart) and then add some new data for subsequent months, but that doesn't dynamically append new months to the chart either???

I hope I am explaining this dilemma well. Let me know if you understand what I am asking for and if so, please help me with this. Thank you.
0
Comment
Question by:IEHP1
  • 5
6 Comments
 

Author Comment

by:IEHP1
ID: 38728183
Does anyone have any ideas about how to dynamically update an Excel chart based on the referenced data?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38729595
A sample file would be helpful :)
0
 

Author Comment

by:IEHP1
ID: 38737757
will do when i get the time, thanks
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:IEHP1
ID: 38807674
so I had been recently working with the Excel charts and I found out that my charts will extend to extra months if I select data to extend it farther along the line (in terms of extra month's data).

However, what I really would like to see happen is if when I add the new month's data (month-to-month), it would automatically extend the charts I have in place (it is a combo chart--2 line charts and a varying number of 100% stacked column charts).

I have the data in its own separate tab and formatted as tables, but shouldn't that make it so that when I add the new month's data, it would extend the graph automatically without having to reselect every single data chart (I have to do this for up to about 6 or 7 charts per chart and having to manually select the whole range of data all over again times 8 charts\8 tabs = up to 56 lines and 100% stacked column charts!!!)

I will put something together real quick when I get a chance..... haven't forgotten about this open question.
0
 

Accepted Solution

by:
IEHP1 earned 0 total points
ID: 38816853
does the red circle with the white exclamation point mean that this question has been abandoned? What does that symbol mean?
0
 

Author Closing Comment

by:IEHP1
ID: 39065152
cool
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

13 Experts available now in Live!

Get 1:1 Help Now