Need conditional formatting on cells that already have a subtraction formula
I have a column (let's say column X) that subtracts dates in 2 other different columns (let's say S and T) to tell me by color of cell fill if something was delivered on time or late.
In addition, I would like to add conditional formatting to the same column x in a new color that indicates if the delivery was made at all and is past the due date.
First question - Am I able to add conditional formatting to column x since there's already a formula in the column.
Second question - what is the formula for this?
Tried a number of things..
Thanks so much!
worksheet-function
asked Jan 9 at 23:44
GuruGuru
1
add a comment |
I have a column (let's say column X) that subtracts dates in 2 other different columns (let's say S and T) to tell me by color of cell fill if something was delivered on time or late.
In addition, I would like to add conditional formatting to the same column x in a new color that indicates if the delivery was made at all and is past the due date.
First question - Am I able to add conditional formatting to column x since there's already a formula in the column.
Second question - what is the formula for this?
Tried a number of things..
Thanks so much!
worksheet-function
asked Jan 9 at 23:44
GuruGuru
1
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02
add a comment |
I have a column (let's say column X) that subtracts dates in 2 other different columns (let's say S and T) to tell me by color of cell fill if something was delivered on time or late.
In addition, I would like to add conditional formatting to the same column x in a new color that indicates if the delivery was made at all and is past the due date.
First question - Am I able to add conditional formatting to column x since there's already a formula in the column.
Second question - what is the formula for this?
Tried a number of things..
Thanks so much!
worksheet-function
asked Jan 9 at 23:44
GuruGuru
1
I have a column (let's say column X) that subtracts dates in 2 other different columns (let's say S and T) to tell me by color of cell fill if something was delivered on time or late.
In addition, I would like to add conditional formatting to the same column x in a new color that indicates if the delivery was made at all and is past the due date.
First question - Am I able to add conditional formatting to column x since there's already a formula in the column.
Second question - what is the formula for this?
Tried a number of things..
Thanks so much!
worksheet-function
worksheet-function
asked Jan 9 at 23:44
GuruGuru
1
asked Jan 9 at 23:44
GuruGuru
1
asked Jan 9 at 23:44
GuruGuru
1
asked Jan 9 at 23:44
GuruGuru
1
asked Jan 9 at 23:44
GuruGuru
1
1
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02
add a comment |
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02
add a comment |
1 Answer
1
active
oldest
votes
With conditional formatting you can combine only formats that don't rule each other out. For example you can have one format that makes the cell fill red when condition X is true, and another conditional format that makes the text bold if condition Y is true. If set up correctly, the cell will show red fill and bold text independently, so that when both X and Y are true, it will show bold text on red fill.
If, however, your second conditional formatting rule also changes the cell fill, you will need to determine with rule "wins" when both X and Y are true. This is achieved by changing the order of the rules or with the "Stop if true" setting in the Conditional Formatting Manager dialog.
You can, however, set up the second conditional format in a neighboring column, so that you have several fill color flags side by side in different columns.
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
add a comment |
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%2f1392509%2fneed-conditional-formatting-on-cells-that-already-have-a-subtraction-formula%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
With conditional formatting you can combine only formats that don't rule each other out. For example you can have one format that makes the cell fill red when condition X is true, and another conditional format that makes the text bold if condition Y is true. If set up correctly, the cell will show red fill and bold text independently, so that when both X and Y are true, it will show bold text on red fill.
If, however, your second conditional formatting rule also changes the cell fill, you will need to determine with rule "wins" when both X and Y are true. This is achieved by changing the order of the rules or with the "Stop if true" setting in the Conditional Formatting Manager dialog.
You can, however, set up the second conditional format in a neighboring column, so that you have several fill color flags side by side in different columns.
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
add a comment |
With conditional formatting you can combine only formats that don't rule each other out. For example you can have one format that makes the cell fill red when condition X is true, and another conditional format that makes the text bold if condition Y is true. If set up correctly, the cell will show red fill and bold text independently, so that when both X and Y are true, it will show bold text on red fill.
If, however, your second conditional formatting rule also changes the cell fill, you will need to determine with rule "wins" when both X and Y are true. This is achieved by changing the order of the rules or with the "Stop if true" setting in the Conditional Formatting Manager dialog.
You can, however, set up the second conditional format in a neighboring column, so that you have several fill color flags side by side in different columns.
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
add a comment |
With conditional formatting you can combine only formats that don't rule each other out. For example you can have one format that makes the cell fill red when condition X is true, and another conditional format that makes the text bold if condition Y is true. If set up correctly, the cell will show red fill and bold text independently, so that when both X and Y are true, it will show bold text on red fill.
If, however, your second conditional formatting rule also changes the cell fill, you will need to determine with rule "wins" when both X and Y are true. This is achieved by changing the order of the rules or with the "Stop if true" setting in the Conditional Formatting Manager dialog.
You can, however, set up the second conditional format in a neighboring column, so that you have several fill color flags side by side in different columns.
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
With conditional formatting you can combine only formats that don't rule each other out. For example you can have one format that makes the cell fill red when condition X is true, and another conditional format that makes the text bold if condition Y is true. If set up correctly, the cell will show red fill and bold text independently, so that when both X and Y are true, it will show bold text on red fill.
If, however, your second conditional formatting rule also changes the cell fill, you will need to determine with rule "wins" when both X and Y are true. This is achieved by changing the order of the rules or with the "Stop if true" setting in the Conditional Formatting Manager dialog.
You can, however, set up the second conditional format in a neighboring column, so that you have several fill color flags side by side in different columns.
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
answered Jan 10 at 1:01
teylynteylyn
17.3k22539
17.3k22539
add a comment |
add a comment |
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%2f1392509%2fneed-conditional-formatting-on-cells-that-already-have-a-subtraction-formula%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
(1) Yes, a cell can contain a formula and independently have conditional formatting. (2) I’m too lazy to bother to understand your data. Write a formula that determines whether the delivery was made at all and is past the due date, and you’re done.
– Scott
Jan 10 at 0:43
Is this basically what you want?
– 3D1T0R
Jan 10 at 18:53
Or is this closer to what you're looking for?
– 3D1T0R
Jan 10 at 20:02