Charts that update automatically

[BusinessPlanner]

Hello,

In Excel 2010 I have columns of data representing 13 period in a year (P1 - P13). These columns repeat every year and I have several years of data - so a lot of columns! Each period a number of faults is recorded in the corresponding cell.

I have a chart that shows the data from the columns in a rolling 13 period graph. In P3 for example, the graph shows values from P3 last year to P3 of the current year (see table below). Each period I have to go in to the chart and alter the series and axis to look at the next period along. So in period 4 I will need to go in and change it from P3 - P3 to P4 - P4.

I have several dozen of these graphs and so I would like to set them up to automatically move along and look at the correct periods when new period data is added. I suspect there may be a way to do this using the OFFSET function but I cant find any examples where rolling data is displayed.

Any assitance or advice would be appreciated!

Thanks!

Year

Y1

Y2

Period

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

Faults

x

x

<tbody>
</tbody>

 

[Asala42]

The idea you're shooting for I believe is to define named ranges that use OFFSET (one for the X range and another for Y), then redirect your chart's SERIES formula to use the named ranges.

As an aside, if P3 to P3 is what you are charting, that's actually 14 points.
In any case, I'll assume Y1 P1 through Y2 P3 are currently populated. Later entries are blank cells waiting to be populated.

If you check your chart's SERIES formula currently, it probably looks something like this:

=SERIES(,Sheet1!$D$2:$Q$2,Sheet1!$D$3:$Q$3,1)

Sheet1!$D$2:$Q$2 corresponds to your X-Range
Sheet1!$D$3:$Q$3 corresponds to your Y-Values

Create 2 new named ranges with the following formulas. I'll call them XRange and YRange.

XRange = =OFFSET(Sheet1!$A$2,0,MAX(1,COUNTA(Sheet1!$A$3:$AA$3)-14),1,14)
YRange = =OFFSET(Sheet1!$A$3,0,MAX(1,COUNTA(Sheet1!$A$3:$AA$3)-14),1,14)

These two named ranges will dynamically change to the latest 14 entries. Note that this particular offset logic requires no blank entries before or within your populated data, and no entries in your future data.

Now you just need to replace the arguments in the SERIES formula on your chart.

This:  =SERIES(,Sheet1!$D$2:$Q$2,Sheet1!$D$3:$Q$3,1)
Becomes:  =SERIES(,'test 1.xlsx'!XRANGE,'test 1.xlsx'!YRange,1)

Note the workbook name is present in the updated version.

HTH

 

[BusinessPlanner]

Thank you so much for you help - this seems like it will definitely give me what Im after! However, I cant seem to find where to edit the SERIES formula.

My chart source data looks like this: =Input!$B$31:$B$32,Input!$BE$31:$BR$32

That is the 'Chart Data Range' and you are correct, this points to the latest populated 14 periods (P3 - P3 in this case). I cant seem to find any step that refers to SERIES in order to change it to the arguments you outline.

Also - should I be naming the entire range of data (i.e. all years, all periods including future cells) for the Y axis? And the entire range of periods 1 - 13 recurring over the years for the X axis?

As your solution seems like it will be handy for lots of things I need to do would it be possible for you to explain what each bit of the OFFSET / COUNTA function you provided is doing?

Thanks again

 

[Asala42]

Select your chart, and then click on one of the lines (or bars if a bar chart). In your formula bar you should see a SERIES formula. That's what you would be editing.

Is the range actually a pivot table? That might explain why you can't edit the data series, pivot charts are a lot more restrictive. If that's the case I think you may have to resort to having a dummy range tucked away somewhere that uses direct links to the cells in your pivot table, then have your offset functions and chart work off that, though they'd have to be modified.

To explain OFFSET, it has 5 arguments
=OFFSET(Sheet1!$A$3,0,MAX(1,COUNTA(Sheet1!$A$3:$AA$3)-14),1,14)

The YRange name above applied to your example would result in the range D3:Q3 (or P3 - P3).

(arg1) A range, often a single cell, that defines a starting reference point.
In this case: Sheet1!$A$3. In your example, that's the cell containing the word "Faults"

(arg2) A numeric row offset.
In this case: 0. D3:Q3 is the same row as the reference point, so no row offset is needed.

(arg3) A numeric column offset.
In this case: MAX(1,COUNTA(Sheet1!$A$3:$AA$3)-14)
This is where we do the work. Basically we're counting the non-blanks in A3:AA3 and subtracting 14. 17 values are entered, so this pushes the reference range (17-14) = 3 columns to the right. (A3 to D3 - which is the first P3 point). The MAX stuff is to keep the offset from going below "1", so the X and Y range will start at Column B even if there are fewer than 14 points.

(arg4) Range height.
In this case: 1. Only 1 row.

(arg5) Range width
In this case: 14. This expands the reference point from D3 to D3:Q3 (14 columns wide).

HTH

 

[BusinessPlanner]

SUCCESS!

Working perfectly now. Thanks very much, this is going to save hours of time! Much appreciated!