Range Names convert to Cell References

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?!
 
Thanks for the response Joel, but that only converted the cell reference from
A1 format to R1C1 format. The files still replace the range name with a cell
reference when edited.

Joel said:
There is an option in the Tools Menu - Option - General Tab - Use R1C1
reference Style

AlanC said:
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?!
Click to expand...
Click to expand...
 
I would compare all the option on your PC at work with your lap top to see if
there arre other differences. I didn't see any other option that would give
your symptoms.

AlanC said:
Thanks for the response Joel, but that only converted the cell reference from
A1 format to R1C1 format. The files still replace the range name with a cell
reference when edited.

Joel said:
There is an option in the Tools Menu - Option - General Tab - Use R1C1
reference Style

AlanC said:
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?!
Click to expand...
Click to expand...
Click to expand...
 
Joel, Agreed. The only difference I can see is that the laptop at home and
all the ones in the past ran on a version of Windows, currently XP, and the
application at work is running on a server using Windows Server 2003. But if
that were the problem, then I'd be able to copy the files to the XP laptop
and the problem should go away. It doesn't. Two sets of files on the same
laptop, one created in Windows XP the other Windows Server. One retains the
external range names in formulas, the other does not. I'm at a loss. But
you're thinking along the same lines as I, that it has to be a setting
somewhere and it's sticking with the file when its moved.

Joel said:
I would compare all the option on your PC at work with your lap top to see if
there arre other differences. I didn't see any other option that would give
your symptoms.

AlanC said:
Thanks for the response Joel, but that only converted the cell reference from
A1 format to R1C1 format. The files still replace the range name with a cell
reference when edited.

Joel said:
There is an option in the Tools Menu - Option - General Tab - Use R1C1
reference Style

:

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?!
Click to expand...
Click to expand...
Click to expand...
 
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.


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?!
Click to expand...
 
Was the option ever set :Use R1C1 reference Style? Maybe soemthing from this
being set is still left over in the workbook. Try manually changing a cell
location the has the wrong style to normal style. Save the wrokbook and see
if the R1C1 reappears. Also try to move the modified file to the lap top.

I have had problems with excel un-remembering items. I have links in files
that havve been removed but when I open the worksheet it still asks me to
update the links.

AlanC said:
Joel, Agreed. The only difference I can see is that the laptop at home and
all the ones in the past ran on a version of Windows, currently XP, and the
application at work is running on a server using Windows Server 2003. But if
that were the problem, then I'd be able to copy the files to the XP laptop
and the problem should go away. It doesn't. Two sets of files on the same
laptop, one created in Windows XP the other Windows Server. One retains the
external range names in formulas, the other does not. I'm at a loss. But
you're thinking along the same lines as I, that it has to be a setting
somewhere and it's sticking with the file when its moved.

Joel said:
I would compare all the option on your PC at work with your lap top to see if
there arre other differences. I didn't see any other option that would give
your symptoms.

AlanC said:
Thanks for the response Joel, but that only converted the cell reference from
A1 format to R1C1 format. The files still replace the range name with a cell
reference when edited.

:

There is an option in the Tools Menu - Option - General Tab - Use R1C1
reference Style

:

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?!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.


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?!
Click to expand...
Click to expand...
 
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???).


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 said:
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.


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?!
Click to expand...
Click to expand...
Click to expand...
 
Thanks, but it seems to happen on either the Server or the PC that connects
to the server, even with the files transferred to its local HD. Also if I
transfer the files to the home PC, the problem goes with them. One thing I
just thought of is that the server has an add-in from Global Software
(glbsoft.com) called Spreadsheet Server used to access data in a JDE
installation. I hadn't considered that it could be the problem because the
files in question don't use that add-in and the problem moved with the file
to other computers. Should I post a thread with Global or Spreadsheet Server
in the subject?

Dave Peterson said:
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???).


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 said:
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?!
Click to expand...
Click to expand...
Click to expand...
 
Before you post that new message, maybe you can disable that addin to see if the
problem goes away.

If the problem goes away, maybe you can include that in your followup--it might
make it nice for the next victim, er, user who has this problem.

If you want to disable all the macros (and toolbar customizations), you can:
close excel
windows start button|Run
excel /safe

And then file|open your workbook to test it.
Thanks, but it seems to happen on either the Server or the PC that connects
to the server, even with the files transferred to its local HD. Also if I
transfer the files to the home PC, the problem goes with them. One thing I
just thought of is that the server has an add-in from Global Software
(glbsoft.com) called Spreadsheet Server used to access data in a JDE
installation. I hadn't considered that it could be the problem because the
files in question don't use that add-in and the problem moved with the file
to other computers. Should I post a thread with Global or Spreadsheet Server
in the subject?

Dave Peterson said:
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???).


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.

:

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?!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top