Excel: Formula to Chart not updating

Check the offset formula in col IX is it pointing to correct sheet?  Is the resulting range correct?

Then check code to ensure it references sheet then address.  If you copied the code you may need to change thar



Dave

I doubled checked and couldn't find anything.  I also noticed that it is not updating when I add new data for the new week.  Settings in excel are the same... hmm

There are no series in the chart, so there is a fatal error in sub UpdateSummaryChartArray when you try to set the properties of .SeriesCollection(1)

The macro is made more complicated by the 3D chart you are trying to create. What is supposed to be on the plot?

If you have Excel 2010 (or 2003), you can record the macro and see what the code needs to look like. If you have Excel 2007, then the recorded macros aren't working with chart objects.

I figured out the reason for not updating I need the formula to get Week 5, not the actual letters.

I still need to figure out why the formula is not pulling the last 8 cells though.

When I stepped through the code, the range was working correctly. The problem was as I described, and occurred right after this block of code:

        If Err.Number = 0 And Not IsEmpty(myRng) Then
            With Sheets("Summary Dashboard").ChartObjects("Chart 113").Chart
                If i = 0 Then 'set x-axis labels only once
                    Set myRange = Range(myRng)

Select allOpen in new window

Brad

So why is it that this chart is any different than the ones in the other sheets?  I copied the good charts and renamed it...  It should work?

Okay so the issue now is that the formula is not correct.  I added fixed cell ranges in red "Executive Rollup" and the chart is working.  Any ideas on how to fix the formula?  thanks  

Attached file has working chart NOC-Reports-r12.xlsm

ok what exactly where is not working?

Dave

Last col should reference the last  8 weeks dynamically.

This technic will help when I create formulas for choosing week 1, 1 month, 3 months etc.

last column - give me an address of the last col - what sheet and where?

It would be useful to know what tab that chart is on...

Dave

sorry - Summary Dashboard - got it

hang in there a sec.

Dave

which chart are we concerned about - can't find a chart 113/????

Dave

Oops yeah I modified that chart on the last update. Summary dashboard biggest chart in the middle

and when you click in the listbox, you're not getting the right range?  what range should it be (sorry, just got in)..?

Dave

It should always show the last 8 entries for each row with every new entry added.

Ok.  I'm looking at Summary Dashboard - the main chart.  When the listbox item is selected, its picking up the right rangevalue in column IZ of Executive Rollup Data

When I look at the ranges in Executive Rollup Data, based on Executive Range, those ranges reflect the last 8 values that exist in the row - so the range values look right...

However, when I look at the ranges in Summary Delta Range (those you have as RED) they are what the chart is charting - and they are incorrect...

They also seem to be values not ranges that dynamically change....

Are you wanting to plot the % change values or the regular (above values with the GREEN dynamic range?)  Do I fix the chart to plot what's on the bottom, or what's on the top?

If on the top, then we just need to reference the green ranges from the red area (quick patch).

Let me know,

Dave

It should always show the last 8 entries for each row with every new entry added.

Dave.. Sorry I was stuck on the road..  I will take a look at all your comments and get back with you.  Thanks for your fast answers!!

Dave,

Your IDKey works perfect!  That is so cool how you came up with that.. However, the main issue is still has not been resolved.  Here is a way to understand what I am trying to explain.

When you go down the listbox1 in Summary Dashboard you will notice that the chart's last week will change from week 8  to randon weeks.  The goal is that the formula should always show the week of the most furthest value to the right. So basically all the graphs in the spreadsheet should be at week 5.  Checkout the screencast below.  

Here are my other thoughts.  Since you have experienced ideas on how to work magic in Excel, let me express my thoughts on what my future plans are for this dashboard.

The dashboard is going to be used to allow users to to see all the data that we use in our Key Indicator Report.  I am new to this job and I thought that it would be cool to show in graph form the historical trnds of what the KIR is capturing.  Therefore, I came up with this idea of creating a dasboard.  As the week went by I started getting ideas on what is the best way to display useful information to the managers.  

Functions of the dashboard:
I would like to have the ability to change the chart data to 1w, 1m ,3 m, 6m, and 1yr.  
If It was possible to get a working chart that would demonstrate this I could build the rest of the dashboard.

I am not sure if the way that present formulas are designed to build something of this nature.  I am just greatful for your help because none of this would be possible without your brain power.

:)  Lance

LANCE-S-P-408712.flv

were you voicing anything on the video?  Couldn't hear anything

Dave

No voice.. I just wanted to show you that the weeks are randomly changing..

Randomly?  Now this I gotta see.  First, if you don't mind, I'm doing some housekeeping with your spreadsheet...

Dave

lol well not randomly, let say just accurately misguided.  Poor little formula..   :(

Independent of the weeks generated, is it plotting the correct data? if you click on the series and see the address, then go to the datasheet and reference the address - is it the correct row of data?

Dave

What I have found is that it is selcting the correct row, example row 7 but the range of the data it is plotting is wrong. If you click on ID# E1, the week should end wiith week 5 and the value 63.  Currently it is pulling week 4 , value 72.  

Ok - look on any of your charts, when you select that element, that's what you get.  You need to think back to your original OFFSET formula in column IX

Its not the chart, its the OFFSET range - it is different for EACH element you're plotting, because of the # of values that have or don't have data and where they are in the weeks.

If you agree, we can chat about that.  If not, then I'm still puzzled.

Dave

Ok - I just figured it out....

Hang on.

Dave

I THOUGHT I had it figured out.  Again, take a look at the offset formula and let me know - its different on every row - is the COUNTA supposed to be fixed on every row - ex., tied to the first row, or is it supposed to vary?

I'm off for a while.

Dave

Will check shortly.  We can also write a formula that will pull the range based on the current week.

Or based on the date row..