Excel Chart failing to redraw 1

[SteveCop2]

I have a number of Excel files which are populated by data exported from Access. One of the files is behaving most oddly! It's charts have data tables on them and the data tables show different figures from the source data. Opening the file for the first time this month, I see that the data table shows the correct figures for April, the wrong figures for March and that the graph has only plotted up to March anyway, ignoring the April data! This seems to be a re-drawing issue as it is possible to correct by changing a figure (any figure) in the source data and also if I make a copy of the chart the copy is correct but the original still wrong!

The spreadsheet has automatic calcuation, not manual. I made a copy of the file but it too is faulty. I have considered re-creating the file from scratch but this is hours of work and should not be necessary. None of my other files misbehave so the basic techniques are OK. Any ideas please?

 

[SkipVought]

Hi,

Check the Source Data.

Activate the chart - right click in the chart and select Source Data.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[SteveCop2]

Thanks, Skip. Had alrady tried virtually every possible option but all without success. In fact it is possible by making a copy of the chart on another page to get two charts both showing different results from the same data. Since first posting I have tried opening and repairing the file without success. So I am now beginning to wonder if the issue is that the source data is updated by Access (whilst obviously Excel is closed) and that Excel does not bother to refresh the graph because it does not know the data has changed. Can't really believe this is true, though.

So I've taken a sledgehammer approach - using an auto-open macro so that, when the Excel file opens, it copies and re-pastes the source data and this does indeed result in the chart re-drawing correctly. Not very elegant but at least it solves the immediate problem. If anyone has any further insight though I'd still be very grateful.

 

[SkipVought]

Rather than copy 'n' paste, what about using a Query via Data/Get External Data/New Database Query - Access Databases...

Once the QueryTable is inserted in a sheet, all it needs is a data refresh -- can be done on workbook open with a checkbox in Data/Data Range Properties.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[SteveCop2]

I hadn't even thought of this approach! I tend to favour exporting raw data from Access and then presenting/manipulating within Excel because these systems run both on an office system with 20 users/centralised file server and also on various self-contained laptop or desktop systems which are used offsite. Trying to replicate the directory structure on the standalones has been extremely difficult and advanced actions like data linking unreliable. However, I like the sound of your suggestion so it is certainly worth some experimentation. Thank you!