Automatically adjust displayed portion of fixed data series on chart

[ezdizzy]

Hi there. So I have a large series of data (in the screenshot it extends out past the screenshot to column BP). I'd like the graph to only ever show ~10 columns of data, with the latest data point reflected in the second-to-last column. So if the graph looks how it does in the screenshot on day one, then on day two, once I entered data for 10-Apr, the displayed columns in the graph would shift to show from 2-Apr to 10-Apr. I realize I can change the series, but I'm curious if there is a way to leave the series untouched, but have the range displayed in the chart adjust automatically based on either the date or the data available in the series... Not sure if this makes sense...?

2020-04-10_11-14-27.jpg

[Andy Pope]

You can do that with named ranges.

https://peltiertech.com/dynamic-char...ltiple-series/

[ezdizzy]

Thanks Andy. Does this work when my series is laid out horizontally vs vertically? If so, can you help with how I would be adjusting the Names/Series in that scenario?

[ezdizzy]

Also, how would this offset naming look if I wanted the starting cell to adjust? It looks like the formula in that article always starts with the XLabel of "A" and just grows. I am looking to have the XLabel start with "A" then the next day it starts with "B".

For example, on day one, the xlabel range is A-F. On day two, the xlabel range is B-G. On day three, the xlabel range is C-H. And so on.

[Andy Pope]

To do horizontal dynamic ranges you just need to alter the column start point and length, while the row start = 0 and height =1

MY_DATA: =OFFSET(Sheet1!$D$30,0,COUNT(Sheet1!$30:$30)-MIN(COUNT(Sheet1!$30:$30),9),1,MIN(COUNT(Sheet1!$D$30:$L$30),9)+1)
MY_LABEL: =OFFSET(MY_DATA,-1,0)

You need to calculate the number of available points, capping at 10 points.
Then you can work out the last data point and subtract

[ezdizzy]

So are you saying the following should be in my name manager?

=OFFSET(OFFSET(Sheet1!$D$30,0,COUNT(Sheet1!$30:$30)-MIN(COUNT(Sheet1!$30:$30),9),1,MIN(COUNT(Sheet1!$D$30:$L$30),9)+1),-1,0)

If so, do I leave the 'series' data the same (wherever it may be) for the chart itself?

[Andy Pope]

Did you try the two named ranges I provided?

The use formula to determine column offset and length. The row parameters are simple 0 and 1.

[ezdizzy]

yeah, sorry this is my first time with dynamic charting... when you say "try the named ranges", I'm not clear on where I am inputting what information...

In your post, are "MY_DATA" and "MY_LABEL" names? In other words, in the screenshot below, "XLabels" is to be replaced with what you have for "MY_DATA", and "YAlpha" is to be replaced with what you have for "MY_LABEL"? Is that what you mean?

(screenshot taken from the article you linked earlier in this thread)

Attachment 672396

[Andy Pope]

You need to use the Name Manager on the Formula tab of the ribbon.

Once you have entered the 2 named ranges you can then select the chart and use the Select Data dialog to use the named ranges range than cell references.
The website link provided will provide all the information you need, including screen shots.

[ezdizzy]

Ok, I have figured out the names and where to add them in my series. Now I am trying to understand: Where in these formulas is the chart getting the instruction to shift to the right with the new data? For example: If right now it shows columns D->L, how does it know to show columns E->M, once data is entered into L30?

Attachment 672422

[Andy Pope]

FYI, your attachments are not showing.

The offset function has 5 arguments
Anchor cell
row offset from anchor
column offset from anchor
number of rows
number of columns


MY_DATA: =OFFSET(Sheet1!$D$30,0,COUNT(Sheet1!$30:$30)-MIN(COUNT(Sheet1!$30:$30),9),1,MIN(COUNT(Sheet1!$D$30:$L$30),9)+1)

=OFFSET(
Sheet1!$D$30,
0,
COUNT(Sheet1!$30:$30)-MIN(COUNT(Sheet1!$30:$30),9),
1,
MIN(COUNT(Sheet1!$D$30:$L$30),9)+1
)

The count function determines how many columns there are. The min stops it from tripping up if there are not enough data columns
The number of columns includes +1 as you wanted the last period to be 1 beyond the actual data

[ezdizzy]

yeah something is off in your instructions then (or I am misunderstanding). I had to change the +1 at the end of the formula to a +9 to get the graph to show nine columns. Otherwise, with your formula it was just showing one column in the chart....

[Andy Pope]

It works for me, based on your picture of your data.

[ezdizzy]

Ah. Got it. I found some errors on my end with entering the formulas. Thanks so much for sharing this workbook. Very helpful. I appreciate your patience and help!