A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Named Ranges in Seperate Workbook



 
 
Thread Tools Display Modes
  #1  
Old June 8th, 2004, 10:13 PM
tbieri
external usenet poster
 
Posts: n/a
Default Named Ranges in Seperate Workbook

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
  #2  
Old June 9th, 2004, 01:47 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Named Ranges in Seperate Workbook

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/
_______

tbieri wrote:

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


  #3  
Old June 9th, 2004, 06:02 PM
tbieri
external usenet poster
 
Posts: n/a
Default Named Ranges in Seperate Workbook

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
-----Original Message-----
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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.