Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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?! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
There is an option in the Tools Menu - Option - General Tab - Use R1C1
reference Style "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?! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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" wrote: There is an option in the Tools Menu - Option - General Tab - Use R1C1 reference Style "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?! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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" wrote: 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" wrote: There is an option in the Tools Menu - Option - General Tab - Use R1C1 reference Style "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?! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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" wrote: 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" wrote: 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" wrote: There is an option in the Tools Menu - Option - General Tab - Use R1C1 reference Style "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?! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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" wrote: 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" wrote: 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" wrote: 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" wrote: There is an option in the Tools Menu - Option - General Tab - Use R1C1 reference Style "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?! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Names convert to Cell References
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. AlanC wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell References/Names | Excel Discussion (Misc queries) | |||
Help, change range names back to cell references? | New Users to Excel | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Replace range names with cell references? | Excel Worksheet Functions | |||
Sheet Names and Cell References | Excel Worksheet Functions |