Excel 2007 - Logic behind source file needing to be open for Charts

WAGSciticorp

New Member
Joined
Jun 2, 2009
Messages
2
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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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.<o:p></o:p>
<o:p></o:p>
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.<o:p></o:p>
<o:p></o:p>
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.<o:p></o:p>
<o:p></o:p>
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.<o:p></o:p>
<o:p></o:p>
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.<o:p></o:p>
<o:p></o:p>
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.).<o:p></o:p>
<o:p></o:p>
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?<o:p></o:p>

We can not imagine that this is how Excel is suppose to work, regarding charts and linked data files. How does any large business ever get around this problem, especially when they may have thousands of charts linked to thousands of data files.

Any help would be appreciated. Sorry if the question is overly long.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,857
Messages
5,766,791
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top