Automating Charts in Excel

[WhtChoklat]

I currently have a code that automates the creating of an Excel chart every week from an Access Query. The problem is that it is capturing the rolling data from a table, and I only want the last 12 rows to be graphed. Is there a way I can do this programmatically, so that every week it only graphs the last 12 rows regardless of the amount of records that this table my have?

Thanks,

Ed

 

[xlbo]

A dynamic range name should do this for you

Go insert>name>define and instead of a range reference, enter this:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-12,,12,1)
change Sheet1 to be the name of the sheet with your chart data and change the 1 at the end to be the number of columns across your data set is. This will create a range that moves as you enter or delete data. In this instance, it will cover the last 12 ROWS of data

As an added bonus, if anyone is thinking about doing this the other way round ie if their columns expand instead of the rows, this will create a dynamic named range for the last 12 COLUMNS of data:
=OFFSET(Sheet1!$A$1,,COUNTA(Sheet1!$1:$1)-12,COUNTA(Sheet1!$A:$A),12)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244