I have a spreadsheet with multiple sheets. One of the sheets has historical
data that I update daily. I use this to create graphs and correlation
matrices and such. Right now I have all of my other sheets linked mostly to
the first 40 days of the historical data. What I would like to do is create a
macro that I could either enter a date range into or an amount of days into
an input cell and then have that form the input data that goes into the
graphs, matrices, etc.

For example, instead of being able to only look at the first 40 days of data
in a graph, I would like to look at the data for last June through August. Or
if that is not possible, I would like to pull data from the most recent date
to 80 days past, or 120 days past, for example. And I would like to do this
by simply changing an input cell(s).

Is this possible at all? And if so, could you give me a general idea of how
to go about it? Where can I look to find out how to do this?

Thanks,
Darren

Darren -

its possible, just takes some creativity.

you need:
- input cells
- a dynamic range name
- a change event macro
- a regular macro

i created a sheet (sheet1) with data in A1:B20
one chart on the sheet
2 input cells: one with a date value, the other with how many days I want to
chart

the input cells are F4 = the start date, F5 = the # of days
click insert|names and add a name "mydata"; in the cells that it refers to
is:

=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$F$4,Sheet1!$A$1: $A$20,0)-1,0,Sheet1!$F$5,2)

this starts a range @ $a$1, uses the MATCH function to look up the date in
F4 as the starting cell and uses value in F5 to see how many rows the range
should be.
it is defined as 2 rows wide
the range this defines changes everytime you change the values in F4 and or
F5

in VBA on the sheet object insert a macro to fire on the change eventL

Private Sub Worksheet_Change(ByVal Target As Range)
AdjRange
End Sub

On a regular module inser the macro

Sub AdjRange()
With Sheet1
.ChartObjects(1).Chart.SetSourceData
Source:=.Range(Names("mydata").RefersToRange.Addre ss)
End With
End Sub

each time the worksheet changes (presumably when you change the date or # of
days), the AdjRange macro fires
the macro set the sourcedata range for the chart equal to the range defined
by "mydata"
"mydata" - in turn is defined by the values in F4 and F5

TIPS
You can have a button set to run AdjRange, but its 'slicker' to have it run
by itself when you change the source
You can add error detection, etc to keep the change event from running
AdjRange all the time
THIS WILL NOT WORK ON A WORKSHEET MODULE ALONE
You CANNOT simply set the source data of the chart equal to a dynamic names
range (XL will take it but convert to a range address string)

cheers - voodooJoe


"Darren" wrote in message
...
I have a spreadsheet with multiple sheets. One of the sheets has historical
data that I update daily. I use this to create graphs and correlation
matrices and such. Right now I have all of my other sheets linked mostly
to
the first 40 days of the historical data. What I would like to do is
create a
macro that I could either enter a date range into or an amount of days
into
an input cell and then have that form the input data that goes into the
graphs, matrices, etc.

For example, instead of being able to only look at the first 40 days of
data
in a graph, I would like to look at the data for last June through August.
Or
if that is not possible, I would like to pull data from the most recent
date
to 80 days past, or 120 days past, for example. And I would like to do
this
by simply changing an input cell(s).

Is this possible at all? And if so, could you give me a general idea of
how
to go about it? Where can I look to find out how to do this?

Thanks,
Darren

VoodooJoe,

Thanks for your help so far,

I have a few problems still. Specifically, with the code:

Source:=.Range(Names("mydata").RefersToRange.Addre ss)

in the AdjRange macro. It says it expects an expression and puts the problem
at :=

In addition, if I should happen to get this to work, do you know how I could
be able to adjust the source data for the charts so that they will display
any adjustments in the size of the source data? Is it possible to make a
macro that could influence the source data of a chart?

Finally, if I am trying to look at a correlation with the code:

=CORREL(dailychange!$E$3:$E$36,dailychange!J$3:J$3 6)

Is it possible to simply replace:

dailychange!$E$3:$E$36,dailychange!J$3:J$36

with the "mydata" named range?

Thanks again,
Darren



"voodooJoe" wrote:

Darren -

its possible, just takes some creativity.

you need:
- input cells
- a dynamic range name
- a change event macro
- a regular macro

i created a sheet (sheet1) with data in A1:B20
one chart on the sheet
2 input cells: one with a date value, the other with how many days I want to
chart

the input cells are F4 = the start date, F5 = the # of days
click insert|names and add a name "mydata"; in the cells that it refers to
is:

=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$F$4,Sheet1!$A$1: $A$20,0)-1,0,Sheet1!$F$5,2)

this starts a range @ $a$1, uses the MATCH function to look up the date in
F4 as the starting cell and uses value in F5 to see how many rows the range
should be.
it is defined as 2 rows wide
the range this defines changes everytime you change the values in F4 and or
F5

in VBA on the sheet object insert a macro to fire on the change eventL

Private Sub Worksheet_Change(ByVal Target As Range)
AdjRange
End Sub

On a regular module inser the macro

Sub AdjRange()
With Sheet1
.ChartObjects(1).Chart.SetSourceData
Source:=.Range(Names("mydata").RefersToRange.Addre ss)
End With
End Sub

each time the worksheet changes (presumably when you change the date or # of
days), the AdjRange macro fires
the macro set the sourcedata range for the chart equal to the range defined
by "mydata"
"mydata" - in turn is defined by the values in F4 and F5

TIPS
You can have a button set to run AdjRange, but its 'slicker' to have it run
by itself when you change the source
You can add error detection, etc to keep the change event from running
AdjRange all the time
THIS WILL NOT WORK ON A WORKSHEET MODULE ALONE
You CANNOT simply set the source data of the chart equal to a dynamic names
range (XL will take it but convert to a range address string)

cheers - voodooJoe


"Darren" wrote in message
...
I have a spreadsheet with multiple sheets. One of the sheets has historical
data that I update daily. I use this to create graphs and correlation
matrices and such. Right now I have all of my other sheets linked mostly
to
the first 40 days of the historical data. What I would like to do is
create a
macro that I could either enter a date range into or an amount of days
into
an input cell and then have that form the input data that goes into the
graphs, matrices, etc.

For example, instead of being able to only look at the first 40 days of
data
in a graph, I would like to look at the data for last June through August.
Or
if that is not possible, I would like to pull data from the most recent
date
to 80 days past, or 120 days past, for example. And I would like to do
this
by simply changing an input cell(s).

Is this possible at all? And if so, could you give me a general idea of
how
to go about it? Where can I look to find out how to do this?

Thanks,
Darren