Scroll Bar control not accessing the last 8 month of data

[Alex E.]

I followed the instruction in the TechRepublic article “10 steps to creating a scrolling Excel chart”; http://www.techrepublic.com/blog/10-...l-chart/3454/#. The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.

I also tried a different method (again for a scroll bar and a rolling chart) as outlined in www.seasholes.com/files/Dynamic_Excel_Tips.pdf‎. There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.

I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The ‘magic’ number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages “Ref#”.

In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.

How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.

I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2.

Any suggestions ? What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.

[protonLeah]

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"