Conditional formatting behaving differently on connected table
In my Excel (2016) sheet I have a Source sheet which has a Table with all the data of a year. From this table I want individual sheets for each month. I don't want to be copying everything over every time something changes so I created a connection to another sheet with the data for lets say April. There I have the entire Source table which I can filter. All the data is looking good except for the formatting which I've copied over with a copy/paste on the entire table.
Some of this formatting contains conditional formatting and for the most part this is working good except for conditional formatting on smaller than (red) or greater than (green) 0. For some reason all the data is handled as positive, even on visual negative values. At least this is what I think.
The columns that have this behavior are columns with data in the form of minutes (time) as [m] to allow negative minutes to be displayed. In the source table these conditional formattings are working as expected.
Below are the examples. This first image is the Source sheet table. The second image is the April sheet table.
Something else I've noticed is that when I manually fill something in in these columns, the number 1 for example is translated to 1440. The number -1 is translated to -1440 and the conditional formatting is working on this.
When checking the formula bar where I put in -1 is actually -1. When looking at where I put 1 I get the following: 1/01/1900 0:00:00.
Have I chosen my source data format wrongly? Should I have been werking with integers instead of time?
Off-topic: My final goal is to display a graph of each month and creating the separate tables was to my knowledge the way to go. If there is a better way to do this feel free to link me to an example.
microsoft-excel microsoft-excel-2016 conditional-formatting
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
add a comment |
In my Excel (2016) sheet I have a Source sheet which has a Table with all the data of a year. From this table I want individual sheets for each month. I don't want to be copying everything over every time something changes so I created a connection to another sheet with the data for lets say April. There I have the entire Source table which I can filter. All the data is looking good except for the formatting which I've copied over with a copy/paste on the entire table.
Some of this formatting contains conditional formatting and for the most part this is working good except for conditional formatting on smaller than (red) or greater than (green) 0. For some reason all the data is handled as positive, even on visual negative values. At least this is what I think.
The columns that have this behavior are columns with data in the form of minutes (time) as [m] to allow negative minutes to be displayed. In the source table these conditional formattings are working as expected.
Below are the examples. This first image is the Source sheet table. The second image is the April sheet table.
Something else I've noticed is that when I manually fill something in in these columns, the number 1 for example is translated to 1440. The number -1 is translated to -1440 and the conditional formatting is working on this.
When checking the formula bar where I put in -1 is actually -1. When looking at where I put 1 I get the following: 1/01/1900 0:00:00.
Have I chosen my source data format wrongly? Should I have been werking with integers instead of time?
Off-topic: My final goal is to display a graph of each month and creating the separate tables was to my knowledge the way to go. If there is a better way to do this feel free to link me to an example.
microsoft-excel microsoft-excel-2016 conditional-formatting
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07
add a comment |
In my Excel (2016) sheet I have a Source sheet which has a Table with all the data of a year. From this table I want individual sheets for each month. I don't want to be copying everything over every time something changes so I created a connection to another sheet with the data for lets say April. There I have the entire Source table which I can filter. All the data is looking good except for the formatting which I've copied over with a copy/paste on the entire table.
Some of this formatting contains conditional formatting and for the most part this is working good except for conditional formatting on smaller than (red) or greater than (green) 0. For some reason all the data is handled as positive, even on visual negative values. At least this is what I think.
The columns that have this behavior are columns with data in the form of minutes (time) as [m] to allow negative minutes to be displayed. In the source table these conditional formattings are working as expected.
Below are the examples. This first image is the Source sheet table. The second image is the April sheet table.
Something else I've noticed is that when I manually fill something in in these columns, the number 1 for example is translated to 1440. The number -1 is translated to -1440 and the conditional formatting is working on this.
When checking the formula bar where I put in -1 is actually -1. When looking at where I put 1 I get the following: 1/01/1900 0:00:00.
Have I chosen my source data format wrongly? Should I have been werking with integers instead of time?
Off-topic: My final goal is to display a graph of each month and creating the separate tables was to my knowledge the way to go. If there is a better way to do this feel free to link me to an example.
microsoft-excel microsoft-excel-2016 conditional-formatting
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
In my Excel (2016) sheet I have a Source sheet which has a Table with all the data of a year. From this table I want individual sheets for each month. I don't want to be copying everything over every time something changes so I created a connection to another sheet with the data for lets say April. There I have the entire Source table which I can filter. All the data is looking good except for the formatting which I've copied over with a copy/paste on the entire table.
Some of this formatting contains conditional formatting and for the most part this is working good except for conditional formatting on smaller than (red) or greater than (green) 0. For some reason all the data is handled as positive, even on visual negative values. At least this is what I think.
The columns that have this behavior are columns with data in the form of minutes (time) as [m] to allow negative minutes to be displayed. In the source table these conditional formattings are working as expected.
Below are the examples. This first image is the Source sheet table. The second image is the April sheet table.
Something else I've noticed is that when I manually fill something in in these columns, the number 1 for example is translated to 1440. The number -1 is translated to -1440 and the conditional formatting is working on this.
When checking the formula bar where I put in -1 is actually -1. When looking at where I put 1 I get the following: 1/01/1900 0:00:00.
Have I chosen my source data format wrongly? Should I have been werking with integers instead of time?
Off-topic: My final goal is to display a graph of each month and creating the separate tables was to my knowledge the way to go. If there is a better way to do this feel free to link me to an example.
microsoft-excel microsoft-excel-2016 conditional-formatting
microsoft-excel microsoft-excel-2016 conditional-formatting
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
asked Apr 10 '18 at 6:40
KrowiKrowi
1011
1011
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07
add a comment |
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07
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%2f1312470%2fconditional-formatting-behaving-differently-on-connected-table%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%2f1312470%2fconditional-formatting-behaving-differently-on-connected-table%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
excel.officetuts.net/en/training/how-excel-stores-date-and-time
– Máté Juhász
Apr 10 '18 at 8:07