I still don't have any guesses--but if it happens on one pc, but not the other,
maybe it's not excel.
Maybe something bad is happening to the network connection on the troublesome
pc.
It kind of makes sense to me that if excel has trouble establishing that
connection back to the sending workbook, that it might revert back to what it
knows (and I'm assuming that it knows what the range name address is--I don't
know if that's true, but it seems to fit???).
AlanC wrote:
Thanks for your thoughts Dave. I can't reproduce the problem either on one
laptop... but it is reproducible on the work laptop. Both the laptops are
running Windows XP Pro (the work one is version 5.1.12600 SP2 build 2600),
but the files are created and stored via a remote connection to a server
running Windows Server 2003 Enterprise x64 Edition v5.2.3790 SP1 Build 3790.
All three machines are running MS Excel 2003 SP2 as part of a Office
Professional installation.
I'm not sure what you mean by dynamic range, but the range names in question
are all defined using the "Define Names" dialog box and contain standard
=Sheet1!$A$1 references.
The referenced file contain 104 range names, all alpha, no special
characters and few if any less than 8 characters long. All are subject to
the same problems.
One of the two original problems seems to have fixed itself after
persistently selecting the "Update remote references" and "Save external link
values" options on the calculation options dialog. The refering file no
longer replaces the range names when the file is opened while the referenced
file is already opened. That's a major plus. But I still have the problem
with any edits removing the range name reference. I can protect the file and
lock down those cells, but I honestly despise that protection feature, both
setting it up and managing it afterwards. I have a number of "self
proclaimed" Excel experts that will be using the files and know that if I
can't fix this and don't lock down the formulas, eventually someone will do
something that will "break" the link, then break the file and then the design
will get blamed.
I'm still hoping someone can come up with a system setting that's causing
this. Thanks again.
"Dave Peterson" wrote:
I couldn't reproduce this behavior in xl2003 in my simple tests.
You may want to include the version of excel that you're running. And how you
defined that named range in Salesdetail.xls--is it a dynamic range or just
B29:N29?
If you create another name (SalesSummaryA(??)), does the same thing happen?
This may not help you find the problem, but maybe you'll see something unique
about that name:
Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:
You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp
If you find the solution, please post back so that Google will have it for the
next person.
AlanC wrote:
For years I've successfully built Excel models that use range names defined
in one workbook in formulas in other workbooks. I.E.; the range
"SalesSummary" in SalesDetail.xls is used in Income.xls in the formula
"Index(SalesDetail.xls!SalesSummary,0,Columns($B$2 :B$2)". The benefits is
that you can do virtually anything to the structure of SalesDetail.xls, that
doesn't change the dimensions of the named range, and Income.xls will always
pull out the right data.
I started a new job at a new company and every time I build an external
range name formula, it converts to the R1C1 format. I.E. the formula above
becomes "Index([SalesDetail.xls]Sales!B29:N29,0,Columns($B$2:B$2)" when
"Sales Summary" is the range B29:N29 on sheet Sales.
When I enter the formula using the external reference range name, IF the
"linked to" file is closed the range name remains a part of the formula. But
then if either the formula is edited with the "linked to" file open OR,
horror of all horrors, if the "linked to" file is already open when the
"linked from" file is opened, all external references are redefined to cell
references.
I have a personal laptop with some of my original files that DO NOT
automatically change range names to cell references on it. Both computers
use the same version of Excel and all the options I can think of to check are
the same. I've copied the new files onto the old laptop and can open both
old and new at the same time... the new files continue to replace the range
name with cell references and the old files continue to save the range names.
Any ideas?!
--
Dave Peterson
--
Dave Peterson