range setting

[Mike Harris]

i want to chart the last n entries in a column in an excel chart. the
total number grows. so, I want to chart the last entry and the
preceding 64. how would I do that?

thanks

mike

 

[Tom Ogilvy]

Use a dynamic named range using the offset function in the refers to part of
the name definition (Insert =>Name=>Define).

Make that (the defined name) the source for your chart.

 

[Don Guillett]

Assuming you are on sheet2
Create a defined range name by
insert>name>define>type in a name such as last64
in the refers to box type in
=offset($a$1,counta($a:$a)-64,0,64,1)
now create a chart with a series =Sheet2!last20
Chart will now be self adjusting

 

[Stein Kristiansen]

What will be the syntax for defining the same range inside a VBA sub
routine?

Regards
Stein

 

[Don Guillett]

The object of doing this is that you don't have to re-define the range. It
is automatic.

 

[Mike Harris]

Thanks, that sure looks like it will work.

Mike Harris