Dynamic Range Charts Across Worksheets?

Jon,
Is it possible to refer to a set number of cells that shifts down each
day? For example, I am plotting VHLC in four columns on a worksheet
and on a separate worksheet I am graphing the latest 20 weeks worth of
data. Each day the graph advances one day (but drops the oldest day
off the chart) when the new data is entered. So the chart remains the
same size, but the data moves across from right to left as the days go
by and new data is entered.
Then this is done for the next worksheet and another chart is added
below the first, etc.
I have all the code working for creating the charts and formatting
them correctly, but the dynamic ranges are new to me. So far what
I've researched appears to define the starting cell in the code (...&
ws.Name & "'!$J$1...) and goes down from there. I guess I'm wanting
my range to advance down the data so I can keep my old numbers (but
not chart them after 20 weeks).
Hope this makes sense, because it's sure tough to put into words for
me!
Thanks,
Jim


Jon Peltier wrote in message ...
As the man says, turn on the macro recorder. I discovered the critical
typo: Your formula leaves out the = in front of OFFSET. Either of these
work:

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
"=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
& ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
"=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
& ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"

I did both A1 and RC notation, because the recorder uses RC. I converted
to A1 to see whether that was the problem, and both worked. Then I
noticed the missing "=".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:
The best way, as far as I am concerned, to get the correct code is to
turn on the macro recorder, create a named formula, turn off the macro
recorder and replace the hard-coded sheet name with ws.name (with the
associated adjustments of double quotes). As a precaution, I would
also ensure that the sheet name has a space in it. That way the XL-
generated code will have the very important single quotes already in
the appropriate places.

Jim -

I have an example on my site that shows how to chart the last 12 months
of data. The first and last plotted point in the chart both move through
the data. You can adapt it to show the last 20 weeks.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

On Tushar Mehta's site (http://tushar-mehta.com) there are several
examples of dynamic ranges that can be used for different ways to
represent a changing data set.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jim wrote:

Jon,
Is it possible to refer to a set number of cells that shifts down each
day? For example, I am plotting VHLC in four columns on a worksheet
and on a separate worksheet I am graphing the latest 20 weeks worth of
data. Each day the graph advances one day (but drops the oldest day
off the chart) when the new data is entered. So the chart remains the
same size, but the data moves across from right to left as the days go
by and new data is entered.
Then this is done for the next worksheet and another chart is added
below the first, etc.
I have all the code working for creating the charts and formatting
them correctly, but the dynamic ranges are new to me. So far what
I've researched appears to define the starting cell in the code (...&
ws.Name & "'!$J$1...) and goes down from there. I guess I'm wanting
my range to advance down the data so I can keep my old numbers (but
not chart them after 20 weeks).
Hope this makes sense, because it's sure tough to put into words for
me!
Thanks,
Jim


Jon Peltier wrote in message ...

As the man says, turn on the macro recorder. I discovered the critical
typo: Your formula leaves out the = in front of OFFSET. Either of these
work:

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
"=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
& ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
"=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
& ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"

I did both A1 and RC notation, because the recorder uses RC. I converted
to A1 to see whether that was the problem, and both worked. Then I
noticed the missing "=".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

The best way, as far as I am concerned, to get the correct code is to
turn on the macro recorder, create a named formula, turn off the macro
recorder and replace the hard-coded sheet name with ws.name (with the
associated adjustments of double quotes). As a precaution, I would
also ensure that the sheet name has a space in it. That way the XL-
generated code will have the very important single quotes already in
the appropriate places.

Thanks Jon, I just went to your site and I think I can adapt it quite easily.
Thank you for your help again, you guys do a wonderful job on this newsgroup!!
Jim
Jon Peltier wrote in message ...
Jim -

I have an example on my site that shows how to chart the last 12 months
of data. The first and last plotted point in the chart both move through
the data. You can adapt it to show the last 20 weeks.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

On Tushar Mehta's site (http://tushar-mehta.com) there are several
examples of dynamic ranges that can be used for different ways to
represent a changing data set.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jim wrote:

Jon,
Is it possible to refer to a set number of cells that shifts down each
day? For example, I am plotting VHLC in four columns on a worksheet
and on a separate worksheet I am graphing the latest 20 weeks worth of
data. Each day the graph advances one day (but drops the oldest day
off the chart) when the new data is entered. So the chart remains the
same size, but the data moves across from right to left as the days go
by and new data is entered.
Then this is done for the next worksheet and another chart is added
below the first, etc.
I have all the code working for creating the charts and formatting
them correctly, but the dynamic ranges are new to me. So far what
I've researched appears to define the starting cell in the code (...&
ws.Name & "'!$J$1...) and goes down from there. I guess I'm wanting
my range to advance down the data so I can keep my old numbers (but
not chart them after 20 weeks).
Hope this makes sense, because it's sure tough to put into words for
me!
Thanks,
Jim


Jon Peltier wrote in message ...

As the man says, turn on the macro recorder. I discovered the critical
typo: Your formula leaves out the = in front of OFFSET. Either of these
work:

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
"=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
& ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
"=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
& ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"

I did both A1 and RC notation, because the recorder uses RC. I converted
to A1 to see whether that was the problem, and both worked. Then I
noticed the missing "=".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

The best way, as far as I am concerned, to get the correct code is to
turn on the macro recorder, create a named formula, turn off the macro
recorder and replace the hard-coded sheet name with ws.name (with the
associated adjustments of double quotes). As a precaution, I would
also ensure that the sheet name has a space in it. That way the XL-
generated code will have the very important single quotes already in
the appropriate places.