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?!
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?!