Solved

Excel:  Formula to Chart not updating

Posted on 2011-02-11
32
391 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Maliki Hassani
  • 16
  • 14
  • 2
32 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok what exactly where is not working?

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Last col should reference the last  8 weeks dynamically.
0
 

Author Comment

by:Maliki Hassani
Comment Utility
This technic will help when I create formulas for choosing week 1, 1 month, 3 months etc.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
sorry - Summary Dashboard - got it

hang in there a sec.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
which chart are we concerned about - can't find a chart 113/????

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Oops yeah I modified that chart on the last update. Summary dashboard biggest chart in the middle
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
It should always show the last 8 entries for each row with every new entry added.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
It should always show the last 8 entries for each row with every new entry added.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
were you voicing anything on the video?  Couldn't hear anything

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
No voice.. I just wanted to show you that the weeks are randomly changing..
0
 
LVL 41

Expert Comment

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

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
lol well not randomly, let say just accurately misguided.  Poor little formula..   :(
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - I just figured it out....

Hang on.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:Maliki Hassani
Comment Utility
Will check shortly.  We can also write a formula that will pull the range based on the current week.
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Or based on the date row..
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

17 Experts available now in Live!

Get 1:1 Help Now