• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating Graph based on different monthly tables in same worksheet

A

Andrea51

New Member
Hello,
I need to be able to have a graph updated on a monthly basis (calling it "Current Month"). The graph should remain in the same location in the worksheet. The data for the graph will change completely month by month (not the series names, just the data). The previous month data must remain on its worksheet - no entering new data on top of old data. The client wants all work in the same workbook and does not want to use a template. I've attached the initial draft of the workbook with just two separate tabs (there will be 28 when all is said and done). The final goal is to have the YTD and Current Month charts linked to a PowerPoint that can be updated by changing the data in Excel only. I've done quite a bit of research and the closest thing I can come up with is the OFFSET function, but that doesn' work well as the data is not sequential. I froze A thru M panels and that gives me enough room to have the graphs while letting the user enter the monthly data by scrolling horizontally. I need to figure out how I can change the "Current Month" graph by selecting a month's data. I was hoping for something like an "IF" statement of some sort, such as - IF (Cell O1=JANUARY2017, create Column Graph of data from Cell M2 toQ20); and then I'd just need to change the reference to the MONTH and that would change the cell. I don't know VBA so this is the closest idea I've been able to come up with. Can anyone help? Maybe there is a completely different approach that I need to take... I've attached my Test file. Thank you!
 

Attachments

  • Test-VP1.xlsx
    72.6 KB · Views: 2
This is just a pointer for you to adapt and develop. No VBA.
In the attached on sheet VP1-TEST, observe that the current chart is titled March 2017, and because you have no data for March, there's nothing plotted.
Now go to cell O25 and choose January from the dropdown.
Observe the chart now has data plotted and the title has changed.

How:
Dates in row 1 are real dates formatted to show month and year only.
In Name manager there is one Subs name which has a formula:
=OFFSET('VP1-TEST'!$A$1,2,MATCH('VP1-TEST'!$O$25,'VP1-TEST'!$1:$1,0)-1,18)

…which is the values of the Substantiated column for a given month.
What range this actually represents is determined by what's in O25.

The other names (UnSub and Pend) are merely offsets of Subs, and are used to plot the other series.
The chart title is simply linked to cell O25.
 

Attachments

  • Chandoo33228Test-VP1.xlsx
    70.9 KB · Views: 6
I'm back - I reformatted the dates on all tables, and I was able to deconstruct the naming but still haven't figured out how the data is linking to the charts. I linked the chart title to the graphs but in other tabs, the data doesn't follow - the month name changes, but the data stays the same. I feel like a dunce but need more guidance. Can you please break down the steps for me? I'm pretty sure I'm missing something simple but can't figure out what. When I try to recreate the setup on other tabs, it does not work, and as I mentioned in my original post, I will have 28 tabs when all is said and done.
One other question - on your "Pend" name, you have the Scope as "Workbook." Should it be changed to "VP1" since it should refer to only one particular worksheet? Sorry to be a pest!
Thanks!
 
Andrea51 said:
but still haven't figured out how the data is linking to the charts.
Click to expand...
Right-click on a chart and choose Select data and see what I've put in there.


Andrea51 said:
One other question - on your "Pend" name, you have the Scope as "Workbook." Should it be changed to "VP1" since it should refer to only one particular worksheet? Sorry to be a pest!
Click to expand...
Yes it should.
 
I was able to get the column charts working correctly and I thank you. Now I'm stuck with the pie chart. The way I have the data plotted on the monthly tables has the columns in Row 2 and the total sums for each of the columns in Row 20. As I need this data to show as a pie chart, I have not been able to figure out how to get the OFFSET formula to work. I do not need the series on Column M for the pie, just each title (leader to contractor) and the total. I can create the pie chart by selecting the titles and only the totals, but can't figure out how to do make it work the way I need it to. Thanks in advance.
 
You're my hero! I got this think now and learned a whole lot in the process. Again, your help is much appreciated! Thank you!
 
You must log in or register to reply here.
Back
Top