Solved: Create chart based on current month

[aminul]

Hello,

I am a newbie to VBA and i've been trying to figure out how to update a graph based on the current month and then looking back six months in total. No luck!

I've attached a spreadhsheet to show what I'm trying to do. Basically, every month the spreadsheet will bring in data from another spreadsheet, and i would like the chart to update, but only by taking into account the past 6 months of data.

Please can anyone show me how I would do this using VBA?

Many thanks,

Aminul

[Aussiebear]

Please have another go at posting the workbook? Click on Go Advanced, scroll down to "Manage Attachments" and follow the prompts from there.

[aminul]

Thanks aussiebear. I've attached the file.

[geekgirlau]

I've created some dynamic range names for the data source and the source of the x-axis labels. The range for "High" for example, as been defined as

=OFFSET(Sheet1!$A$2,MATCH(TODAY(),Sheet1!$A$3:$A$28,TRUE)-5,1,6,1)

• The OFFSET function allows you to create dynamic ranges that vary either the starting point, or the count of rows and/or columns.
• Our starting point for defining the range is cell A2 on Sheet1.
• The MATCH function searches for the closest date to today and returns the index number of the relevant cell. In this example it would return 11, which is the cell for September 2010.
• After MATCH gives a result of 11, we subtract 5. Basically this tells Excel that the starting row for the range is 5 rows above the current month.
• 1 tells Excel that the column for this range starts 1 cell to the right of our starting point of A2.
• 6 sets the total number of rows in our range.
• 1 sets the total number of columns in our range.

I've changed the chart so that the data source for each series now uses these range names. The ranges will dynamically shift each month without you having to change anything.

[aminul]

that works brilliantly! thank you very much geek girl lau!