Excel changes formulas (on save?)
up vote
0
down vote
favorite
Sorry if the following is impenetrable, but I haven't been able to find a similar example:
I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the range name in them.
So, a formula looks like
=IF(correctrange=blank,blank,formula)
but I save and then reopen and it has changed to
=IF(randomname=blank,blank,formula)
The change is consistent (that is, there are various "correctrange" variables that change, but they all change to a consistent (yet different) "randomrange").
I also note that all the "correctrange" variables are columns and they all change to a row referenced range name (don't know if that's going to be a cause).
Anyone else seen this on sometime similar?
microsoft-excel worksheet-function
edited Nov 21 at 22:59
Rey Juna
5379
asked Nov 21 at 22:47
James
1
add a comment |
up vote
0
down vote
favorite
Sorry if the following is impenetrable, but I haven't been able to find a similar example:
I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the range name in them.
So, a formula looks like
=IF(correctrange=blank,blank,formula)
but I save and then reopen and it has changed to
=IF(randomname=blank,blank,formula)
The change is consistent (that is, there are various "correctrange" variables that change, but they all change to a consistent (yet different) "randomrange").
I also note that all the "correctrange" variables are columns and they all change to a row referenced range name (don't know if that's going to be a cause).
Anyone else seen this on sometime similar?
microsoft-excel worksheet-function
edited Nov 21 at 22:59
Rey Juna
5379
asked Nov 21 at 22:47
James
1
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Israndomnamean existing name (that you defined yourself), or is this a new name that popped up from nowhere?
– Peter K.
Nov 21 at 23:24
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Sorry if the following is impenetrable, but I haven't been able to find a similar example:
I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the range name in them.
So, a formula looks like
=IF(correctrange=blank,blank,formula)
but I save and then reopen and it has changed to
=IF(randomname=blank,blank,formula)
The change is consistent (that is, there are various "correctrange" variables that change, but they all change to a consistent (yet different) "randomrange").
I also note that all the "correctrange" variables are columns and they all change to a row referenced range name (don't know if that's going to be a cause).
Anyone else seen this on sometime similar?
microsoft-excel worksheet-function
edited Nov 21 at 22:59
Rey Juna
5379
asked Nov 21 at 22:47
James
1
Sorry if the following is impenetrable, but I haven't been able to find a similar example:
I have a (large) model that uses lots of range names. Recently, on saving, the formulas are changing the range name in them.
So, a formula looks like
=IF(correctrange=blank,blank,formula)
but I save and then reopen and it has changed to
=IF(randomname=blank,blank,formula)
The change is consistent (that is, there are various "correctrange" variables that change, but they all change to a consistent (yet different) "randomrange").
I also note that all the "correctrange" variables are columns and they all change to a row referenced range name (don't know if that's going to be a cause).
Anyone else seen this on sometime similar?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Nov 21 at 22:59
Rey Juna
5379
asked Nov 21 at 22:47
James
1
edited Nov 21 at 22:59
Rey Juna
5379
asked Nov 21 at 22:47
James
1
edited Nov 21 at 22:59
Rey Juna
5379
edited Nov 21 at 22:59
Rey Juna
5379
edited Nov 21 at 22:59
Rey Juna
5379
5379
asked Nov 21 at 22:47
James
1
asked Nov 21 at 22:47
James
1
asked Nov 21 at 22:47
James
1
1
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Israndomnamean existing name (that you defined yourself), or is this a new name that popped up from nowhere?
– Peter K.
Nov 21 at 23:24
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44
add a comment |
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Israndomnamean existing name (that you defined yourself), or is this a new name that popped up from nowhere?
– Peter K.
Nov 21 at 23:24
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Is
randomname an existing name (that you defined yourself), or is this a new name that popped up from nowhere?– Peter K.
Nov 21 at 23:24
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Is
randomname an existing name (that you defined yourself), or is this a new name that popped up from nowhere?– Peter K.
Nov 21 at 23:24
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377428%2fexcel-changes-formulas-on-save%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you test saving in an alternative format (eg Excel 97/.xls) to see if the same corruption occurs? Also, check the formulae after saving, but before exiting.
– AFH
Nov 21 at 22:59
Can you give some more information? How large is the workbook? How many names do you approx. use? Are they all at workbook level, or do you also use names specific for individual worksheets? Which OS and Excel version do you use? Is
randomnamean existing name (that you defined yourself), or is this a new name that popped up from nowhere?– Peter K.
Nov 21 at 23:24
Could you provide a sample or screenshot?
– Lee
Nov 22 at 8:48
@James,, it's unusual behavior difficult to fix it without using the live Sheet. If possible mail me the File ☺
– Rajesh S
Nov 22 at 12:00
Thanks for the responses. I am coming to the conclusion that it is the number of range names (okay, there are 66K+ already) - but the confusion is more around how the problem has manifested itself (actually changing the references used in a formula when saving or opening) The model is ~48Meg (it is being both expanded - more regions in the model - but shrunk - by using range names rather than standard formulas - but doubling the number of regions in offset by the use of range names). All the range names are defined as global in the model.
– James
Nov 22 at 22:44