Excel, linked WorkSheets causing “0” to appear in blank cells
I am working on a project which involves 14 sales consultants each with a client pipeline. These 14 sheets are in a Workbook (Workbook A) that has 1 Sheet to consolidate all the information into one pipeline.
Each consultant has an individual WorkBook that they fill in and the information is then actively transferred to Workbook A from their individual Workbook Sheets.
The problem I have is that I used a direct link (=Sheet1), because of this method I used I have to set the amount of cells to link.
All the cells I have linked with no information returns a zero in Workbook A which then causes my Consolidation page to bring in all the rows with "0" in and it does not look nice.
I would like to prevent the Consolidation sheet to import these 0 figures from the consultant sheets.
This information is constantly being refreshed so the filter also gets refreshed which means I cannot simply hide it by deselecting the value on the filter.
excel excel-formula
edited Nov 26 '18 at 19:50
Jimi
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
add a comment |
I am working on a project which involves 14 sales consultants each with a client pipeline. These 14 sheets are in a Workbook (Workbook A) that has 1 Sheet to consolidate all the information into one pipeline.
Each consultant has an individual WorkBook that they fill in and the information is then actively transferred to Workbook A from their individual Workbook Sheets.
The problem I have is that I used a direct link (=Sheet1), because of this method I used I have to set the amount of cells to link.
All the cells I have linked with no information returns a zero in Workbook A which then causes my Consolidation page to bring in all the rows with "0" in and it does not look nice.
I would like to prevent the Consolidation sheet to import these 0 figures from the consultant sheets.
This information is constantly being refreshed so the filter also gets refreshed which means I cannot simply hide it by deselecting the value on the filter.
excel excel-formula
edited Nov 26 '18 at 19:50
Jimi
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
add a comment |
I am working on a project which involves 14 sales consultants each with a client pipeline. These 14 sheets are in a Workbook (Workbook A) that has 1 Sheet to consolidate all the information into one pipeline.
Each consultant has an individual WorkBook that they fill in and the information is then actively transferred to Workbook A from their individual Workbook Sheets.
The problem I have is that I used a direct link (=Sheet1), because of this method I used I have to set the amount of cells to link.
All the cells I have linked with no information returns a zero in Workbook A which then causes my Consolidation page to bring in all the rows with "0" in and it does not look nice.
I would like to prevent the Consolidation sheet to import these 0 figures from the consultant sheets.
This information is constantly being refreshed so the filter also gets refreshed which means I cannot simply hide it by deselecting the value on the filter.
excel excel-formula
edited Nov 26 '18 at 19:50
Jimi
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
I am working on a project which involves 14 sales consultants each with a client pipeline. These 14 sheets are in a Workbook (Workbook A) that has 1 Sheet to consolidate all the information into one pipeline.
Each consultant has an individual WorkBook that they fill in and the information is then actively transferred to Workbook A from their individual Workbook Sheets.
The problem I have is that I used a direct link (=Sheet1), because of this method I used I have to set the amount of cells to link.
All the cells I have linked with no information returns a zero in Workbook A which then causes my Consolidation page to bring in all the rows with "0" in and it does not look nice.
I would like to prevent the Consolidation sheet to import these 0 figures from the consultant sheets.
This information is constantly being refreshed so the filter also gets refreshed which means I cannot simply hide it by deselecting the value on the filter.
excel excel-formula
excel excel-formula
edited Nov 26 '18 at 19:50
Jimi
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
edited Nov 26 '18 at 19:50
Jimi
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
edited Nov 26 '18 at 19:50
Jimi
8,57741934
edited Nov 26 '18 at 19:50
Jimi
8,57741934
edited Nov 26 '18 at 19:50
Jimi
8,57741934
8,57741934
asked Nov 26 '18 at 18:49
JohannJohann
32
asked Nov 26 '18 at 18:49
JohannJohann
32
asked Nov 26 '18 at 18:49
JohannJohann
32
32
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Maybe try using a =isblank formula to prevent the blank cells from causing you to see the 0's.
Something like this maybe =IF(ISBLANK(Sheet1!'YOURCELLSHERE),'','Sheet1!Cell')
answered Nov 26 '18 at 18:56
JacobJacob
997
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
add a comment |
Go to File|Options|Advanced and scroll down to "display options for this worksheet" and un-check "Show a zero in cells that have zero value". Keep in mind that this will cause cells that should display a "0" to not display that "0".
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53487283%2fexcel-linked-worksheets-causing-0-to-appear-in-blank-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Maybe try using a =isblank formula to prevent the blank cells from causing you to see the 0's.
Something like this maybe =IF(ISBLANK(Sheet1!'YOURCELLSHERE),'','Sheet1!Cell')
answered Nov 26 '18 at 18:56
JacobJacob
997
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
add a comment |
Maybe try using a =isblank formula to prevent the blank cells from causing you to see the 0's.
Something like this maybe =IF(ISBLANK(Sheet1!'YOURCELLSHERE),'','Sheet1!Cell')
answered Nov 26 '18 at 18:56
JacobJacob
997
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
add a comment |
Maybe try using a =isblank formula to prevent the blank cells from causing you to see the 0's.
Something like this maybe =IF(ISBLANK(Sheet1!'YOURCELLSHERE),'','Sheet1!Cell')
answered Nov 26 '18 at 18:56
JacobJacob
997
Maybe try using a =isblank formula to prevent the blank cells from causing you to see the 0's.
Something like this maybe =IF(ISBLANK(Sheet1!'YOURCELLSHERE),'','Sheet1!Cell')
answered Nov 26 '18 at 18:56
JacobJacob
997
answered Nov 26 '18 at 18:56
JacobJacob
997
answered Nov 26 '18 at 18:56
JacobJacob
997
answered Nov 26 '18 at 18:56
JacobJacob
997
997
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
add a comment |
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
This worked perfectly, thank you. Sometimes the most simple things are made complicated when you don't see a solution straight away.
– Johann
Nov 26 '18 at 20:19
add a comment |
Go to File|Options|Advanced and scroll down to "display options for this worksheet" and un-check "Show a zero in cells that have zero value". Keep in mind that this will cause cells that should display a "0" to not display that "0".
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
add a comment |
Go to File|Options|Advanced and scroll down to "display options for this worksheet" and un-check "Show a zero in cells that have zero value". Keep in mind that this will cause cells that should display a "0" to not display that "0".
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
add a comment |
Go to File|Options|Advanced and scroll down to "display options for this worksheet" and un-check "Show a zero in cells that have zero value". Keep in mind that this will cause cells that should display a "0" to not display that "0".
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
Go to File|Options|Advanced and scroll down to "display options for this worksheet" and un-check "Show a zero in cells that have zero value". Keep in mind that this will cause cells that should display a "0" to not display that "0".
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
answered Nov 26 '18 at 19:10
Frank BallFrank Ball
60139
60139
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
add a comment |
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
1
1
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
Hi Thank you for trying to assist. I have tried that but unfortunately I have cells with the value "0" and need some areas to display a "0". Jacob's answer solved my problem.
– Johann
Nov 26 '18 at 20:30
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53487283%2fexcel-linked-worksheets-causing-0-to-appear-in-blank-cells%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
