Dynamic named range linked to a scrollbar

[Elza]

Hello there,

I am trying to place a scrollbar on to the chart through "Dynamic named range/OFFSET function" but my OFFSET formula doesn't work for some reasons. Could someone help me with this?

[lmp]

Where is the scrollbar? In what way to you want to scroll? Do you want a fixed number of data points or a varying number of data points in the chart?

I added two scrollbars to the sheet, one to manipulate the number of data points and another one to manipulate the start of the chart.

The file uses these names

chtStart =Sheet1!$J$5 -- this is where the start scrollbar stores its value
chtWidth =Sheet1!$J$2 -- this is where the number of points scrollbar stores its value
G3304NA_period =OFFSET(Sheet1!$B$5,chtStart,0,chtWidth,1)
Dates =OFFSET(G3304NA_period,0,-1)

The chart's X axis values have been set to =Sheet1!Dates
The chart's series values have been set to =Sheet1!G3304NA_period

[Elza]

Sorry for insufficient information.
I will be adding a new date every week to the table, so I want the chart to present the last 3 months/6 months/12 months of data and to be able to scroll through all data points as well. I think i need to manipulate the number of data points. I hope i explained clearly. Please let me know if it is still doesn't make sense to you.

I appropriate your help.

[lmp]

With the chart sample I uploaded you can adjust the number of data points and the start of the chart. Is that not what you want?

[Elza]

LMP,
It is not exactly what i needed. The "Data Points" scroll bar at the "less" side starts presenting data from the FIRST date (28-Sept-2012) and then adds next dates by scrolling to the "more" side. I need quite opposite - to start from the LAST entered date (which is currently 11-Jan-2013 but a new date will be added every week,so i need to be included every new date) and to scroll towards to the first date, which is 28-Sept-2012.
Could you please take a look again at my injuiry?

[lmp]

Delete the lower scroll bar and the named range for chtStart. Change the formula for the series range name to

G3304NA_period =OFFSET(Sheet1!$B$5,COUNT(Sheet1!$A:$A)-chtWidth,0,chtWidth,1)

[Elza]

This is amazing! It works the way I need! Thank you very much for help and being responsive. One more question. If I need to add other series besides the G3304NA, shell I create the same Offset formula for each of them?
How do the "Dates" and "G3304NA_period" named ranges work together? Could you please explain?
Dates =OFFSET(G3304NA_period,0,-1)
G3304NA_period =OFFSET(Sheet1!$B$5,COUNT(Sheet1!$A:$A)-chtWidth,0,chtWidth,1)

[lmp]

Here's how they work:

G3304NA_period =OFFSET(Sheet1!$B$5,COUNT(Sheet1!$A:$A)-chtWidth,0,chtWidth,1)

Offset(range,row,column,height,width)
Start in cell B5, go down so many rows: count the numbers in column A and subtract the value of chtWidth, go 0 columns to the right, set the height of the range to the number stored in chtWidth, set a width of 1

Dates =OFFSET(G3304NA_period,0,-1)
Start with the range G3304NA_period, go 0 rows down, go 1 column to the left

So, you can use the G3304NA_period range name as the basis for an offset formula Offset(G3304NA_period,rows,columns) where a negative value for rows moves the result up and a negative value for columns moves the result to the left.