Excel: Formula to Chart not updating

Experts:

I have tried to fix this issue but this particular chart  "113" is not updating with the listbox "1" in the "summary dashboard"  

After researching I noticed that the formula in "Executive Rollup Data" column IZ is not pulling correctly.  The formula should dynamically know to pull the last 8 cells each row.  I have played around with the formulas and still can't figure it out.  

Thanks
NOC-Reports-r12.xlsm
Maliki HassaniAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
I have to run.  Assuming my analysis of your worksheet is correct.. here's what I have done

The RED ranges in Executive Rollup Data (WERE FIXED - not dynamic and...) are now referencing the green one's above.  I assume you're using the data at the bottom for the listbox only, not the chart.  so we go from the value lookup of the range in the lower portion to get the range which represents the data in the upper portion. and that's what gets charted.

Since the range addresses in the upper portion are what you want to plot (I surmise), and do get last 8 data entries, I set the lower ranges equal to the upper ranges that calc dynamically.Here it is attached.

I'll check back in a couple hours - have to go.

Dave
NOC-Reports-r13.xlsm
0
 
dlmilleCommented:
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
0
 
Maliki HassaniAuthor Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
byundtCommented:
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.
0
 
Maliki HassaniAuthor Commented:
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.
0
 
byundtCommented:
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)

Open in new window



Brad
0
 
Maliki HassaniAuthor Commented:
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?
0
 
Maliki HassaniAuthor Commented:
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
0
 
dlmilleCommented:
ok what exactly where is not working?

Dave
0
 
Maliki HassaniAuthor Commented:
Last col should reference the last  8 weeks dynamically.
0
 
Maliki HassaniAuthor Commented:
This technic will help when I create formulas for choosing week 1, 1 month, 3 months etc.
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
sorry - Summary Dashboard - got it

hang in there a sec.

Dave
0
 
dlmilleCommented:
which chart are we concerned about - can't find a chart 113/????

Dave
0
 
Maliki HassaniAuthor Commented:
Oops yeah I modified that chart on the last update. Summary dashboard biggest chart in the middle
0
 
dlmilleCommented:
and when you click in the listbox, you're not getting the right range?  what range should it be (sorry, just got in)..?

Dave
0
 
Maliki HassaniAuthor Commented:
It should always show the last 8 entries for each row with every new entry added.
0
 
dlmilleCommented:
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
0
 
Maliki HassaniAuthor Commented:
It should always show the last 8 entries for each row with every new entry added.
0
 
Maliki HassaniAuthor Commented:
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!!
0
 
Maliki HassaniAuthor Commented:
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
0
 
dlmilleCommented:
were you voicing anything on the video?  Couldn't hear anything

Dave
0
 
Maliki HassaniAuthor Commented:
No voice.. I just wanted to show you that the weeks are randomly changing..
0
 
dlmilleCommented:
Randomly?  Now this I gotta see.  First, if you don't mind, I'm doing some housekeeping with your spreadsheet...

Dave
0
 
Maliki HassaniAuthor Commented:
lol well not randomly, let say just accurately misguided.  Poor little formula..   :(
0
 
dlmilleCommented:
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
0
 
Maliki HassaniAuthor Commented:
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.  
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
Ok - I just figured it out....

Hang on.

Dave
0
 
dlmilleCommented:
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
0
 
Maliki HassaniAuthor Commented:
Will check shortly.  We can also write a formula that will pull the range based on the current week.
0
 
Maliki HassaniAuthor Commented:
Or based on the date row..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.