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
fficeffice" /><o></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
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.
fficeffice" /><o></o><o
></o>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
></o><o
></o>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
></o><o
></o>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
></o><o
></o>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
></o><o
></o>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
></o><o
></o>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
></o><o
></o>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
></o>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.
