Named Ranges in Seperate Workbook

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

tbieri

Hi,

I have 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). I am getting odd behavior and don't
understand how or what order excel looks at different
workbooks. If both the Summary and Source workbooks are
open, life is grand. In some cases, if I close the Source,
the series disappear from the Summary chart. In other
cases, the series don't disappear. Any suggestions as to
how to handle this problem?

Regards
Tim
 
Tim -

I did a little experiment. I created a chart in one book based on data
from another. I made two series, one using a named range, the_Y, that
was directly based on a worksheet range, the other that was defined in
the Define Name dialog as offset one column from the first range,
the_Y2=OFFSET(the_Y,0,1). The first range survived the source book being
closed, but the second did not. I went to Links on the Edit menu, and
tried to update the links, and the error message told all:

Microsoft Excel cannot find 'the_Y2' on 'source.xls'. There are two
possible reasons:

• The name you specified may not be defined.
• The name you specified is defined as something other than a
rectangular cell reference.
Check the name and try again.

The second bullet tells you just what Excel is able to read from a
closed file: a rectangular cell reference.

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

Thanks for the information. I think I found the source of
my error, the name references were messed up, pointing to
the Summary instead of the Source.

For example, the named range in the Source would be Xvalues
and defined as 'SheetName'!$A$1:$A$10. I also used
Yvalues1,2 and 3, which were offset from Xvalues.

When I looked in the Summary workbook, there would be
defined names, which I did not define, probably created
when the graph was copy/pasted. The defined names pointed
back to the Source workbook. Close the Source and bye-bye
pretty graph lines.

I think it is fixed for the moment by changing the series
formula to refer to the Source workbook named range (using
your utilit/marcro) instead of the Summary named ranges,
and deleting the named ranges in the Summary workbook. For
example - Series(,'Source'!Xvalues,'Source'!Yvalues,1). I
have graphs with multiple series using this method.

I still do not update links when I open the Summary book,
prefering to do it manually.

Not sure how clear this came across, please respond if
there are questions.

Regards,
Tim

PS thanks for the great website
 
Back
Top