I would like to set up a chart to show one month of data at a time, but I want it to show the month with the most recent data. Is there a way to set this up to automatically change months, when new data is added? This is a bar chart, that I want to display information for the month of April 2009, 2008, 2007, and 2006. Then when I add May's information, I want the chart to automatically show May data.

So long as the query that your data is based on includes the new month, it will be included.

SHADOW

The information is based on a table in Excel, not a query.

The information is based on a table in Excel, not a query.

Indeed. In my haste I assumed it was Access without seeing that this is in the Excel thread.

The answer should be the same, though.

SHADOW

How do you write a query in Excel?

How do you write a query in Excel?

Well, what I meant is that if your data includes 5 months instead of 4 months, it should be taking that into account and adding it to the chart. If not, then I'm not sure how you are constructing the chart.

I only want the chart to show one month at a time. I know that if I show all months at once, the new data will be automatically added, but I don't want to show all months at once.

Gotcha...I didn't get that from the original post. Sorry...maybe someone else can help.

Good luck :)

SHADOW

I don't know what your workbook looks like and how you are creating the charts, but it seems to me, though I have never done it, that if you used a macro then it could use the last row or column in the worksheet to create the chart.

Brian

Hi., LadyDi,

Update Charts Automatically When You Enter New Data (http://spreadsheetpage.com/index.php/tip/update_charts_automatically_when_you_enter_new_dat a/) shows the basics with the use of OFFSET.

Ciao,
Holger

Hi All

I've used the link to create a rolling chart which works well for data that runs down the columns.

I've tried to create the same thing for data running across the columns as well but it isn't working so well, it doesn't auto update unless I highlight the whole range & then drag in another period, then it's fine.

I'm also finding that if I delete periods of data, it will revert to just a few days instead of the entire range that's still left??

The Defined name I've added for the Totals is below:

=OFFSET(autochart!$B$58,0,0,,COUNTA(autochart!$B$5 8:$IV$58))

The date defined name is the same apart from the row it reads:

=OFFSET(autochart!$B$57,0,0,,COUNTA(autochart!$B$5 7:IV$57))

I've changed the Range to read from the entire column 'A' to be the entire row from Column 'B' to Column 'IV'

Can anyone advised where this is wrong? It's sort of half working but I'm unsure what's wrong???