Excel 2007 - linked files need to be opened for charts

[WAGSciticorp]

We have an Excel spreadsheet located in directory c:\AAA. This spreadsheet contains 5 or more worksheets, and each of the worksheets contain 1 -4 charts. All of the charts are linked to other Excel spreadsheets/data files.

For example, worksheet-A may contain 4 charts, say: chart-1, chart-2, chart-3 and chart-4. Each chart is linked to a separate spreadsheet, say: ws-01, ws-02, ws-03 and ws-04. And all of these worksheets are located in various directories, say: c:\BBB, c:\CCC, c:\DDD\123, or subdirectories, say: c:\AAA\123\BBB, c:\AAA\456\BBB, etc.

The problem that we are having, and no one seems to know why, nor can find a straight answer… is that at least half of these linked spreadsheets/data files have to be open in order for the charts to be updated. If the spreadsheet containing the charts is opened without the spreadsheets/data files being open, the charts do not reflect the current data, or, vice versa, if any of the linked spreadsheets/data files are closed, the charts no longer show the current data.

Is there a rhyme/reason as to why some linked spreadsheets/data files do not need to be opened, while some do. One of the problems, besides the slow speed of Excel, especially when all of these files are stored on networked drives (though, in our case, all of the associated files are at least contained on the same networked drive), is that in some cases, where the spreadsheet may contain 20 or more charts, we may have to have 15 linked spreadsheets/data files open in order for the charts to reflect the current data. And god help us if we need to have open 2 or 3 spreadsheets, each containing 20 or more charts… … it isn’t reasonable to expect to have to have all of those 15 or 30 or 45 linked spreadsheets/data files opened too.

Obviously, we believe we’re missing something, but again, can not determine through documentation or research on the internet as to what dictates if a chart will have to have the linked spreadsheet/data file open or not.

We have noticed that, as you build your chart, at some point, sometimes, the SWITCH button, where you can switch what is your horizontal and vertical axis data, becomes grayed out… perhaps due to the way we need to select our data for the chart… … but, the end result is that once that SWITCH button becomes grayed out, it’s pretty much a guarantee that that linked spreadsheet/data file will now need to be opened in order for the chart to show the current data.

In some cases, we have added the data file into the same spreadsheet containing the charts, but in the long run, this is not a reasonable approach since we have hundreds of different data files, and automating the inclusion of these data files into the hundreds of different spreadsheets containing charts… would be cumbersome at best, as well as making the spreadsheets containing the charts ridiculously large, and subsequently slower (in opening/changing the sheet your viewing/etc.).

Is there any rules/guidelines regarding when/why a data file will need to be opened when it’s linked to a chart in Excel. Is there a bullet proof way to build/link charts/data files so that the data files don’t need to be opened. What are we missing, and how can we develop going forward?

[Andy Pope]

The Switch button will become grayed out when the data ranges of the series becomes non-contiguous.

You would need to update the links via the ribbon,
Data > Connections > Edit links > Update Values.

This should happen automatically when the chart file is opened. If the Data file changes in the meantime then the Update will be required.

Have you installed SP2?
Are the files native 07 format, xlsx or xlsm, files?

[WAGSciticorp]

1) the data files are updated during processing, but after that, they remain the same until the following month.

i'm not sure i understand why we would have to select "update values" since isn't that the purpose of having files linked so that your chart is automatically updated with the change in the data file. that is, once i open the spreadsheet with the chart which is linked to the data file that has been updated, shouldn't the chart automatically be updated (auto calculation is on).

not sure if you also might have meant that since the data is non-contiguous, that that is the reason why... ... but, we only have a couple of charts with non-continguous data... and the charts with contiguous data, we're still experiencing the problem where we have to have the data file open in order for it to update the chart.



incidently, we are on version SP2 of Windows XP, we are using Excel 2007, and the data files are in .XLSX format.

[Andy Pope]

If you update the data file and save it. Then open the chart file with Updates on automatic then yes the chart should update.

I meant the Office rather than Windows service pack.

[WAGSciticorp]

Unfortunately, that is not the case.

We perform processing on the 1st of the month. Before we open up any of the charts which contain links to these data files, all of the data files have been updated, and they will not be updated again until the following week, or month.

When we open, the charts still reflect old data. If we check the links, they all say "unknown" (to the 5 to 10 source files used). We click check links, and everything reflects "ok". However, the charts still do not reflect the new data.

All of the spreadsheets that have charts are set to automatically update. All of the data files that we use are in 2007 format (.xlsx).

Now, if we open the source files, then all of the charts then update. If we save the charts file, close, close the source files, then reopen the chart file, they again reflect old data... ... ... we have to open the source files again.

This is just ridiculous. We have numerous charts all referencing numerous source files... and to have to have all these source files open is not a working solution... especially for our recipients since they don't have access to the source files.

I plowed through the help file, the options, various websites, and can find pratically nothing regarding this problem - save for making sure automatic updates/automatic calculations is on.

I can not believe that we are the only folks experiencing this problem.