Updating graphs in excel each month

[nutmegy]

Hi

I have a spreadsheet which is a huge table of data which has about 20-30 charts generated from it. The ranges of data are 12 months, so these need to be shifted down each month to take on the new data.

Is there a way in VBA to automate this?

I have tried doing a selection panel on one sheet to select the month and assign the respective values to the charts by individually programming each step into the code but it's way to lengthy.

Any ideas?

 

[PeterMoran]

Hi nutmegy,

Here are two links which may be of help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;183446&Product=xlw

http://j-walk.com/ss/excel/usertips/tip053.htm

The most common situation is where data is being charted for a financial period, and each day there is a new days data at the end of the range. Here only the end of the range is being changed each day.

From what you have indicated you seem to need the 12 months range to have both the start of the chart and the end of the chart change each month.

Hopefully this will provide some ideas towards a satisfactory solution.

Good Luck!
Peter Moran

 

[nutmegy]

Thanks for this Peter

So do you have any ideas on how to make this a moving 12 month range?

Thanks

Andrew

 

[xlbo]

I havn't followed the links but this should be doable with dynamic range names eg if you had a list of months in col A, with one row per month, the following formula for a named range will refer to the latet 12 rows / months (assuming header in A1)

=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-13,,12,1)

I use -13 to include the header in the calc - otherwise it would be -12 (which is more obvious - 12 for 12 months)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nutmegy]

Thanks for that Geoff....

It works a treat...

One final thing

I also have a column with pre-filled targets which need to be moved down a similar way, how would i do that? The above works with new data, is there a way I can offset the selection.

Thanks

 

[xlbo]

If the targets are in col D for example then:

=OFFSET(Sheet1!$D$2,COUNTA(Sheet1!$A:$A)-13,,12,1)

will do the same for the targets


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question