Dynamic Chart scroll bar

[Plumbstar Tom]

Hi

I have looked all over the internet at dynamic charts and scroll bars and every thing is shownwhen the data is in columns, but mine is rows

I cant get my scroll bar to 'scroll' the chart I have

I think the issue is with my defined names

They look like this

='Sheet1'!$E$3:$AZ$3

which works for a dynamic chart but not whnen I want to add a scroll bar

Can any one help?

 

[p45cal]

Change the defined names as follows:
Date:
=OFFSET('NPD Forecast Tracker'!$E$3,0,'NPD Forecast Tracker'!$D$3-1,1,12)

This is a dynamic range dependent on the value in D3, the cell linked to your scroll bar. Using Date as a range name works, but not advised since it's the same as a worksheet function, anyway, I kept it.

The other named ranges I adjusted simply to be offsets of the Date named range - a set number of rows below in each case:
TotalSales:
=OFFSET(Date,1,0)

NPDSales:
=OFFSET(Date,2,0)

NPDForecast:
=OFFSET(Date,3,0)

NPDBudget:
=OFFSET(Date,5,0)

I don't think you need do more except try it out.
It works on your sheet here, but if you can't get it to work we'll have to work out a way to get a copy to you.

 

[Plumbstar Tom]

Hi

I have it working and its great thanks

for some reason my chart, now in its own sheet, will only scroll to NOV-13?

 

[p45cal]

Hi

I have it working and its great thanks

for some reason my chart, now in its own sheet, will only scroll to NOV-13?

Does your scroll bar reach its limit (left/right) when this happens?
It may just be a case of right-clicking the scrollbar, choosing Format Control, going to the Control tab, and adjusting the Maximum value.
Whats the value in D3 when you can't scroll further?

Otherwise post your sheet in box.net again. If it's not for public view then you can Private Message me the link.

 

[Plumbstar Tom]

The D3 value is 12 when at max, i will PM you the new box.net link

 

[p45cal]

Exactly as I thought in post #13, change the max value to at least 36, more to allow for more dates.

 

[Plumbstar Tom]

So simple! thanks so much for your help