• 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.

Macro for updating chart with last week of data

J

jeffm

New Member
Hello,

I'm looking to create a macro button that will update a chart with the last 7 days of information on a data sheet.

If you look on the file I have uploaded the data is in the worksheet named "NP CLK DATA" and the chart in question is on the sheet "NP CLK C3S". There are two charts on this sheet - the one on top with all of the data available and the one below with the last 7 days of data (which I input manually). I'd like a button that will update this chart with the last 7 days of data automatically.

I'm not even sure this is possible but any help or advice is greatly appreciated.

Thank you!
 

Attachments

I guess it would make just as much sense if it was a button that populated some columns to the right of the data on the NP CLK Data page which would then be referenced to by the existing chart. Either way I would be happy.

Even a macro on a seperate sheet that copied all of the last 7 days worth of data from the data sheet would be perfect.
 
Jeffm

The chart is currently displaying data from 1 June to 11 June

When you say update it do you mean update it to (Today - 7) to Today ?

Please clarify your requirements
 
Hi jeffm,

See the changes I made to your workbook. Overall, we want to use a dynamic range, like the one described here:
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Your situation has a slight twist in that each day may have different number of recordings, and varying days. So, I created a helper column first, and then was able to make the dynamic ranges. Now your chart will always plot the last 7 days, in real-time, as you add to the data (no macro needed).

I also took liberty of converting the raw data into an XL table, and using structural references cuts down on processing requirements.
 

Attachments

Luke - that is seriously awesome. Thank you so much. Now I just need to figure out how to get one of these charts made on all of the other worksheets.

Also when I go to name manager and Table1 says it refers to A2 to BN580 - will that update as I put in more data?
 
Correct, the table will automatically grow as you add more data. Benefits of this:
Columns with formulas will auto-fil
Can use a reference like Table1[My Column] and it will automatically adapt with more data!
http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/

The Name Manager, as you saw, has 2 named ranges. The first, ChartLabels, is what does the harder math. Uses a couple of INDEX function to locate beginning and end points of the 7-day period that we want to look at. ChartData then is much simpler, I just tell it to move 14 columns to the right of ChartLabels.
Then, in your chart, select series, edit, and for the X/Y values, can type something like
='Sheet Name'!ChartLabels
and you now have a dynamic chart. :awesome:
 
I'm having a hard time figuring out how to make my charts dynamic.
I want the upper chart in "NP CLK C3S" to update automatically.
I created a name in name manager named chtDate1 which refers to =OFFSET(NP CLK Data!$A$2,0,0,COUNTA(NP CLK Data!$A:$A),1) and another named chtC3S1 which refers to =OFFSET(NP CLK Data!$O$2,0,0,COUNTA(NP CLK Data!$A:$A),1) and I keep getting errors when trying to input the range into my chart.
Any idea?
What I've been trying to put in my chart would be =Mixcharts.xlsx!chtDate1 and =Mixcharts.xlsx!chtC3S1.
 
Are you including the sheet names and single apostrophes?
='NP CLK Data'!chtDate1

Also gets input on the series edit pop-up, not the Chart data range.
E.g.
upload_2014-6-16_12-34-7.png
 
You can try and do the whole workbook name, but keeping the name and references straight that way is harder IMO, so I use the shortcut of using sheet name + named range.
 
I get an invalid references error regardless of if I use workbook or worksheet. Do you think you could just do this on the last file you uploaded and re-upload? That would be amazing. Like I said I'm going to have to go through every chart and do this I just need one to base the rest of them off of.
 
Sure thing Jeff, see attached. If you need the whole column, I suggest using table references as shown in the defined ranges (you'll see it in the name manager how I did it). Let me know if there's anything else we can do!
 

Attachments

You must log in or register to reply here.
Back
Top