Rolling Date Axis Graph from a Complex Data Chart

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
Hi. I have a data chart that shows a number of sets of data, as the small extract below shows;
AS_Revised IBM Weekly Change Stats.xls
BCDEFGHIJKLMNO
1ReportingWeekTotalBAU>6daysnotice<6daysnoticeCancelledRejectedNotUsedFailedOnHoldSuccessfulUnsuccessfulSuccessfulFailed
205/11-11/1133629451012011181112
312/11-18/114403681920401625164
419/11-25/11361028850010316311
526/11-02/1258837215200302528253
603/12-09/12241112128203081383
Example Graphs


I am pulling 5 graphs (100% stacked ones) out of that, showing various comparisons against adjacent colums (hence the duplication at the end...).

I want to be able to have these graphs automatically show just the last X number of weeks each time I open the spreadhseet and enter data against the current week.

As you'll see, the chart above pulls its data from another sheet in the workbook - and to top it off, I want the 5 graphs to be on a different sheet to the chart above.

Does that make sense? I am struggling to understand how I can achieve my desired results, but suspect it has something to do with OFFSET and TODAY forumlas? :confused:

Any help would be much appreciated...
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
Try the following...

First, define the following references...Insert > Name > Define...

Name: NumWeeks
Refers to:
=Sheet1!$Q$2

Name: Dates
Refers to:
=OFFSET(Sheet1!$B$1,MATCH("*",Sheet1!$B:$B,-1)-1,0,-MIN(NumWeeks,MATCH("*",Sheet1!$B:$B,-1)-1))

Name: Total
Refers to:
=OFFSET(Dates,0,1)

Name: BAU
Refers to:
=OFFSET(Dates,0,2)

Name: Greater6Days
Refers to:
=OFFSET(Dates,0,3)

...and so on.

Once you have all of the references defined, you can start creating your charts. For your first chart...

1) Switch to Sheet2

2) Insert > Chart > Finish > OK

3) Enter the formulas for whichever and however many data series/columns you wish. For example:

=SERIES(Sheet1!$E$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Greater6Days,1)

=SERIES(Sheet1!$F$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Less6Days,2)

=SERIES(Sheet1!$G$1,'Dynamic Chart.xls'!Dates,'Dynamic Chart.xls'!Cancelled,3)

...and continue until you're done. You can then change your chart type, format, etc.

Repeat the process for your other charts, again, selecting whichever data series/columns you wish to include.

Cell Q3 on Sheet1 determines how many week's worth of data to display. Change this number to the desired weeks.

If you'd like I can email you a sample file. Let me know if you so wish.

Hope this helps!
 

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
Thanks Domenic, that looks great and I think ( :confused: ) I understand how it's working/what it's doing.

However, where to enter the =SERIES bits is stumping me; when I go to the inserted blank chart on Sheet2 and right click, select Source data and Series, I get error messages when I put the forumlas in any of the 3 boxes (Name, Values or Axis labels). The error message says I have en error in the formula... :(

If you would be as kind as to email me the file you have that works, I'd be extremely grateful; would it show me how to make one of the graphs a stacked 100% bar with data from columns F thru' K in each week? I'm a bit hazy on making it do that too... :oops:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Once you do the 'Insert > Chart > Finish/Ok' bit, it should give you a blank chart window. At this point, all you have to do is begin entering the formula. The formula will automatically be entered in the formula bar. When you're finished entering your first formula, enter the next one, and so on.

Alternatively, you could go through the steps in the Chart Wizard...

1) Insert > Chart

2) Choose Chart Type

3) Choose Data Range tab

4) Series in: Column

5) Choose Series tab

6) Click Add to add the first series

7) Name: =Sheet1!$F$1

8) Values: =Dynamic Chart.xls!Less6Days

9) Category Labels: =Dynamic Chart.xls!Dates

10) Click Add

...and so on.

I'll be happy to send you a sample file. Send me your email address either through Private Message or Email.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
8) Values: =Dynamic Chart.xls!Less6Days

9) Category Labels: =Dynamic Chart.xls!Dates

That should be...

8) Values: ='Dynamic Chart.xls'!Less6Days

9) Category Labels: ='Dynamic Chart.xls'!Dates
 

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126

ADVERTISEMENT

I've sent you a PM, as I am still getting error messages (even with the corrected details. :cry: :confused:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
You'll need to replace the reference to the workbook ('Dynamic Chart.xls') with the name of your own workbook. Or you can replace it with 'Sheet1'.

Also, I've emailed you a sample. Let me know if you don't receive it.
 

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
After some other moments of frustration :oops: that I can now put down to my own stupidity :oops: , this works like a charm.

Many, many thanks Domenic! (y)
 

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
126
After having great success with the above, I need to revisit ad apply it to a chart where the dates are the column headings and the data is in rows accordingly.

My question therefore is, how do I change the following Name definition to run horizontally rather than vertically...? Specifically the last 3 cells with text.

Name: Dates
Refers to:
=OFFSET(Sheet1!$B$1,MATCH("*",Sheet1!$B:$B,-1)-1,0,-MIN(NumWeeks,MATCH("*",Sheet1!$B:$B,-1)-1))
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top