A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Auto-updating charts



 
 
Thread Tools Display Modes
  #1  
Old September 12th 16, 04:13 PM
Jooniper Jooniper is offline
Junior Member
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 13
Red face Auto-updating charts

Hi all,

I am trying to build a chart that only has the last 26 weeks worth of data and updates every week.

So, I would like week 1-26 then 2-27, then 3-28 etc etc.

Any help would be greatly appreciated. I only need to see 26 data points

Many thanks in advance

Jooniper
Ads
  #2  
Old September 12th 16, 06:03 PM posted to microsoft.public.excel.charting
Claus Busch
external usenet poster
 
Posts: 3,454
Default Auto-updating charts

Hi,

Am Mon, 12 Sep 2016 16:13:07 +0100 schrieb Jooniper:

> I am trying to build a chart that only has the last 26 weeks worth of
> data and updates every week.
>
> So, I would like week 1-26 then 2-27, then 3-28 etc etc.


your week numbers in column A, the values in column B.
Create names for both columns:

1) Name = x_Values
RefersTo: =OFFSET(Tabelle1!$A$1,MATCH(MAX(Tabelle1!$A:$A),Ta belle1!$A:$A,0)-1,,-26)
2) Name = y_Values
RefersTo: =OFFSET(x_Values,,1)

Now you can use these names as source for the chart.
Legend entries: =YourWorkbookName!y_Values
Category: =YourWorkbookname!x_Values
The workbook name has to include the file extention.

Regards
Claus B.
--
Windows10
Office 2016
  #3  
Old September 12th 16, 06:17 PM posted to microsoft.public.excel.charting
Claus Busch
external usenet poster
 
Posts: 3,454
Default Auto-updating charts

Hi again,

Am Mon, 12 Sep 2016 19:03:14 +0200 schrieb Claus Busch:

> 1) Name = x_Values
> RefersTo: =OFFSET(Tabelle1!$A$1,MATCH(MAX(Tabelle1!$A:$A),Ta belle1!$A:$A,0)-1,,-26)


if it is possible that you have sometimes less than 26 weeks change the
forumula to:
=OFFSET(Tabelle1!$A$1,MATCH(MAX(Tabelle1!$A:$A),Ta belle1!$A:$A,0)-1,,-MIN(26,COUNTA(Tabelle1!$A:$A)-1))


Regards
Claus B.
--
Windows10
Office 2016
  #4  
Old September 14th 16, 12:51 PM
Jooniper Jooniper is offline
Junior Member
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 13
Default

Thank you very much
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Charts not updating vitorjose Excel Worksheet Functions 1 July 3rd 08 02:36 PM
Updating Charts Mike Charts and Charting in Excel 1 May 9th 07 01:36 PM
updating pie charts Lilsis7 Excel Discussion (Misc queries) 2 April 24th 06 01:21 PM
updating charts Jon Peltier[_8_] Excel Programming 0 August 24th 04 04:20 AM
updating charts Chip Pearson Excel Programming 0 August 20th 04 06:24 PM


All times are GMT +1. The time now is 01:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.