Disappearing Graph Data & Named Ranges

  • Thread starter Thread starter tbieri
  • Start date Start date
T

tbieri

Howdy,

This is a follow up to an earlier post where there is a
Summary workbook that has multiple charts that are
copied/linked to different Source workbooks. The Source
workbook chart is created using dyamic named ranges (e.g.
source_xvalues, source_yvalues).

The problem is/was that if both the Summary and Source were
open then life was grand. If the Source was closed, then
the data in the graph would disappear somtimes. This is
not the case if you are just graphing ranges (e.g.
$a$1:$a$50).

The following steps seem to provide reliable results for
maintaining the graphs in the Summary file without having
the Source file(s) remain open (using WinXP and OfficeXP):

1. Open the Summary File
a. Don't update links
2. Open the Source File
a. Using Dynamic Named Ranges, the file recalculates when
opened
b. Save the Source File
3. Save the Summary File
4. Switch to a different Tab, not sure why, but frequent
crashes if left on Tab with linked graphs
5. Close and Don't Save the Source File
6. In the Summary File, switch to the Tab with linked
graphs and the graphs should be there.

I would be interested in comments/thoughts from the
graphing/excel gurus as to why this is the case and if
there is a simplier/better way to get to the end point.

Regards
Tim
 
Hi Tim -

Excel can extract certain data from a closed workbook, but only if it is
referenced with cell addresses.

Here's one workaround. Copy all the cells you will need on the source
worksheet, then use Paste Special - Links to paste links to these cells
in a new sheet in the target workbook. Use these linked cells for your
dynamic ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,

Thanks for the suggestion. The question that it raised
deals with the Dynamic Named Ranges.

The graphs rely on data that changes (typically increases)
on a weekly basis. This is handled by using the dynamic
nature of the ranges. If I were to copy the current data
and paste the link, the next week there would be more data
and I would have to repeat the process, right?

Regards,
Tim
 
Does the new data come from a predictable (next row in the range) or
measurable (last row in the used range) cell in the other sheet? You can
find it using offsets and so forth in the summary sheet. The paste link
is just a Q&D way to get formulas there, but you're not obliged to leave
them unedited.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top