Using OFFSET function to update dynamic chart with scroll bar, but data does not scroll

I've created a dynamic chart (see attached) and want to utilize a scroll bar so that data corresponding to the chart moves left or right with the click of either end of the scroll bar.

This chart needs to go through to 'Week 52', since it will entail an entire year's worth of data. You can see where I have linked all of my OFFSET functions, and which cells they each represent.

The problem is that when I scroll right on the chart, the data lines just clump together rather scrolling neatly across.

Why is this?

Thanks.

Originally Posted by 6StringJazzer

You have a couple of problems here.

The most obvious is the squishing problem. This happens because your formula in column F leaves a blank "" if the target data does not exist. This will be plotted as a zero value on a blank category. So you get all these zeros at the end, with the meaningful data being compressed at the beginning. To solve this, change your formula to

Formula:

Please Login or Register  to view this content.

and fill down. The NA will be ignored.

Now, you still have a problem. As you scroll, the data still extends out to August 16, and your X axis is set for automatic max, so the data is still plotted along the entire width of the chart. I get the sense that what you really want is for a fixed-scale chart to look like it's sliding back and forth. This is because you start with a chart that can accommodate the entire full data set. Scrolling the scroll bar simply cuts off the beginning of the data set, rather than shifting it. See attached for a different version that has the effect you want, but may not be what you really need.

Seems you've read my mind, as that is exactly what I am looking for.

Thank you!