On Jan 31, 5:03*pm, Xt <the.christ...@clear.net.nz> wrote:
> On Jan 31, 1:23*am, G Ray <gra...@mail.com> wrote:
>
>
>
>
>
> > I've been able to generate the 120 cell range, references in two cells
> > and the concatenate them with ":" between them in a single cell. *Iv'e
> > been able to associate this to a scroll bar.
> > So now I have a cell that contains *"B483:B602". *As I move the scroll
> > bar to the other extreme the cell changes to "B3:B122". *I just cant
> > figure out how to get these changing cell references into the graphs
> > source data. *Is it possible?
>
> > On Jan 29, 7:05*pm, G Ray <gra...@mail.com> wrote:
>
> > > Greetings Everyone,
>
> > > I have a 600 row long data set. *I have several graphs set up to look
> > > at this data.
>
> > > I have put scroll bars on a couple of them. *This allows me to reduce
> > > the number of points being looked at.
>
> > > What I would like to do is create a graph that looks at 120 data
> > > points at a time. *I would like the scroll bar to move the 120 point
> > > refereneces from the top of the data set to the bottom of the set.
>
> > > The idea is to be able to view any 120 consecutive data points within
> > > my data set.
>
> > > Can this be done with worksheet formulas?
>
> > > Thanks
> > > Glen- Hide quoted text -
>
> > - Show quoted text -
>
> It sounds like you need a "dynamic range" for your graph. *You can
> Google it but basically you draw the graph with any range you like.
> Say the data is in the A column with a header. *Then use your slider
> to change a cell, say D1 to the first cell of the data you actually
> want. *Define a range in the name manager, say n using the offset
> function using absolute references which gives the range you want.
> Something like define n to be *=OFFSET($A$1,$D$1,0,120,1)
> Now click on a point on the graph, look at the reference to the data
> in the formula bar and replace the data there with the range n. *Some
> other things may change automatically. *Now when you slide, the range
> changes and the graph too.
>
> This is far a line graph. *Things are only slightly more complicated
> if you want the X axis to change as well with an xy scatter.
>
> This may take a bit of experimenting but it will work.
>
> xt- Hide quoted text -
>
> - Show quoted text -
You could set up 120 cells to be formulas to 600 cells by using
Offset(cell ref, R,C). In one cell you would type the offset amount,
and it would start returning cells that number away from the start of
the data. For example, if your data is in a column and you put in
Offset(cell ref, 0,0), then it will return the 120 cells at the top.
If you use Offset(cell ref, 0,100), then the rows starting at 100 down
will be returned.
Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707
Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
Similar Threads
