Chart scroll bar is not scrolling through entire series, why could this be?

[seanpod]

Hi,

I have attached a spreadsheet in which I have set up a scroll bar that corresponds to a timeline.

The issue is that when you click the arrow on the scroll bar to go all the way to the right, it stops at a certain point in the data series rather than going all the way to the end of the series.

If you type a large number into the row increment, it'll go to where it needs to be and show the correct data on the timeline.

Why isn't the scroll bar working?

[6StringJazzer]

Right-click on the scroll bar and select Format Control, go to the Control tab. You will see that the Maximum value is set to 12. Update it to whatever you want the maximum value to be.

[seanpod]

Thank you for the helpful reply.

You may also notice that in my spreadsheet, beginning at H30 and down, there are cell reference errors, though I think I've referenced everything properly. Why could this be?

[6StringJazzer]

The formulas in columns H:K perform an INDEX on the range $B$5:$E$100, which is 96 rows. The row index is the value in column A plus the scrollbar value. If for any row, the sum of the value in column A plus the scrollbar value exceeds 96, you will get a #REF! error. For example, if the scrollbar is set to 40, you will an error starting in row 61 because the A value is 57. 57+40=97, greater than 96, so can't be indexed.

If you want to suppress those errors you can do something like

Please Login or Register  to view this content.

[seanpod]

The formulas in columns H:K perform an INDEX on the range $B$5:$E$100, which is 96 rows. The row index is the value in column A plus the scrollbar value. If for any row, the sum of the value in column A plus the scrollbar value exceeds 96, you will get a #REF! error. For example, if the scrollbar is set to 40, you will an error starting in row 61 because the A value is 57. 57+40=97, greater than 96, so can't be indexed.

If you want to suppress those errors you can do something like

Please Login or Register  to view this content.

My end goal is to run a timeline through the end of this year, so up until Dec. 31.

When I replace the INDEX statement with the IFERROR statement beginning at H61, and auto-complete the formulas succeeding H61, it definitely gets rid of the cell reference error, but does not seem to be changing the scroll bar in a positive way. I still get #REF on the scroll bar beginning in October.

Should I be applying your formula in columns I, J & K as well? And what about to the first two worksheets?

[6StringJazzer]

I still get #REF on the scroll bar beginning in October. Should I be applying your formula in columns I, J & K as well?

Yes.

And what about to the first two worksheets?

The first two worksheets just have static values. I don't understand this question.