Force error formulas with reference to a specific sheet
I have a complex workbook with many spreadsheets. One of these spreadsheets is called Raw Data. Many of the formulas on the Raw Data spreadsheet reference cells in a hidden spreadsheet called "2018 sheet". I have created a new spreadsheet called "2019 sheet", which is slightly different from "2018 sheet", and changed my formulas in "Raw Data" to now only reference "2019 sheet".
If I were to delete "2018 sheet", then Raw Data would work fine, but I would get errors on any formula throughout the rest of the workbook that references "2018 sheet". What I would like to do is make those formulas error without deleting "2018 Sheet". Something along the lines of 'if a formula references "2018 Sheet", have that cell produce an error'. Essentially I'm trying to disable "2018 Sheet" without deleting it.
Is this possible without VBA? Is it possible with VBA? (This is more a capability question - no need to post code)
microsoft-excel
asked Jan 9 at 17:13
user2800user2800
568
add a comment |
I have a complex workbook with many spreadsheets. One of these spreadsheets is called Raw Data. Many of the formulas on the Raw Data spreadsheet reference cells in a hidden spreadsheet called "2018 sheet". I have created a new spreadsheet called "2019 sheet", which is slightly different from "2018 sheet", and changed my formulas in "Raw Data" to now only reference "2019 sheet".
If I were to delete "2018 sheet", then Raw Data would work fine, but I would get errors on any formula throughout the rest of the workbook that references "2018 sheet". What I would like to do is make those formulas error without deleting "2018 Sheet". Something along the lines of 'if a formula references "2018 Sheet", have that cell produce an error'. Essentially I'm trying to disable "2018 Sheet" without deleting it.
Is this possible without VBA? Is it possible with VBA? (This is more a capability question - no need to post code)
microsoft-excel
asked Jan 9 at 17:13
user2800user2800
568
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30
add a comment |
I have a complex workbook with many spreadsheets. One of these spreadsheets is called Raw Data. Many of the formulas on the Raw Data spreadsheet reference cells in a hidden spreadsheet called "2018 sheet". I have created a new spreadsheet called "2019 sheet", which is slightly different from "2018 sheet", and changed my formulas in "Raw Data" to now only reference "2019 sheet".
If I were to delete "2018 sheet", then Raw Data would work fine, but I would get errors on any formula throughout the rest of the workbook that references "2018 sheet". What I would like to do is make those formulas error without deleting "2018 Sheet". Something along the lines of 'if a formula references "2018 Sheet", have that cell produce an error'. Essentially I'm trying to disable "2018 Sheet" without deleting it.
Is this possible without VBA? Is it possible with VBA? (This is more a capability question - no need to post code)
microsoft-excel
asked Jan 9 at 17:13
user2800user2800
568
I have a complex workbook with many spreadsheets. One of these spreadsheets is called Raw Data. Many of the formulas on the Raw Data spreadsheet reference cells in a hidden spreadsheet called "2018 sheet". I have created a new spreadsheet called "2019 sheet", which is slightly different from "2018 sheet", and changed my formulas in "Raw Data" to now only reference "2019 sheet".
If I were to delete "2018 sheet", then Raw Data would work fine, but I would get errors on any formula throughout the rest of the workbook that references "2018 sheet". What I would like to do is make those formulas error without deleting "2018 Sheet". Something along the lines of 'if a formula references "2018 Sheet", have that cell produce an error'. Essentially I'm trying to disable "2018 Sheet" without deleting it.
Is this possible without VBA? Is it possible with VBA? (This is more a capability question - no need to post code)
microsoft-excel
microsoft-excel
asked Jan 9 at 17:13
user2800user2800
568
asked Jan 9 at 17:13
user2800user2800
568
asked Jan 9 at 17:13
user2800user2800
568
asked Jan 9 at 17:13
user2800user2800
568
asked Jan 9 at 17:13
user2800user2800
568
568
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30
add a comment |
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f1392370%2fforce-error-formulas-with-reference-to-a-specific-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
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.
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%2f1392370%2fforce-error-formulas-with-reference-to-a-specific-sheet%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
Could you just rename "2018 Sheet" to something else while you examine your formulas, then change it back prior to saving the file? This would only work if you are actually referring to the sheet name, wouldn't work if you're using structured table references.
– jrichall
Jan 10 at 19:30