Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 14
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
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3,872
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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3,872
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   Report Post  
Junior Member
 
Posts: 14
Default

Thank you very much
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:05 PM.

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

About Us

"It's about Microsoft Excel"