Mixing
Conditional Formating with if then statement
I have a workbook with 2 sheets, Sheet 1 has Column I. Column I is filled with Percent's. I want these %'s formatted as black or red based on conditional formatting.
Sheet 2 has cell B2 filled in with one of the following "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec."
The conditional Formatting formula I thought would be something along with lines of:
If(Sheet2 cell B2 = "Feb" then If the % in Sheet 1 Column I11-I31 the column is less than 10 divided by 12, color Red.
Then the same formula for each month but 9/12 for March and so on.
Hopefully this makes sense. I appreciate everyone taking a look.
Thank you,
Matt
excel formatting conditional
asked Nov 19 '18 at 15:19
Matt Lane
296
add a comment |
I have a workbook with 2 sheets, Sheet 1 has Column I. Column I is filled with Percent's. I want these %'s formatted as black or red based on conditional formatting.
Sheet 2 has cell B2 filled in with one of the following "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec."
The conditional Formatting formula I thought would be something along with lines of:
If(Sheet2 cell B2 = "Feb" then If the % in Sheet 1 Column I11-I31 the column is less than 10 divided by 12, color Red.
Then the same formula for each month but 9/12 for March and so on.
Hopefully this makes sense. I appreciate everyone taking a look.
Thank you,
Matt
excel formatting conditional
asked Nov 19 '18 at 15:19
Matt Lane
296
add a comment |
I have a workbook with 2 sheets, Sheet 1 has Column I. Column I is filled with Percent's. I want these %'s formatted as black or red based on conditional formatting.
Sheet 2 has cell B2 filled in with one of the following "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec."
The conditional Formatting formula I thought would be something along with lines of:
If(Sheet2 cell B2 = "Feb" then If the % in Sheet 1 Column I11-I31 the column is less than 10 divided by 12, color Red.
Then the same formula for each month but 9/12 for March and so on.
Hopefully this makes sense. I appreciate everyone taking a look.
Thank you,
Matt
excel formatting conditional
asked Nov 19 '18 at 15:19
Matt Lane
296
I have a workbook with 2 sheets, Sheet 1 has Column I. Column I is filled with Percent's. I want these %'s formatted as black or red based on conditional formatting.
Sheet 2 has cell B2 filled in with one of the following "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec."
The conditional Formatting formula I thought would be something along with lines of:
If(Sheet2 cell B2 = "Feb" then If the % in Sheet 1 Column I11-I31 the column is less than 10 divided by 12, color Red.
Then the same formula for each month but 9/12 for March and so on.
Hopefully this makes sense. I appreciate everyone taking a look.
Thank you,
Matt
excel formatting conditional
excel formatting conditional
asked Nov 19 '18 at 15:19
Matt Lane
296
asked Nov 19 '18 at 15:19
Matt Lane
296
asked Nov 19 '18 at 15:19
Matt Lane
296
asked Nov 19 '18 at 15:19
Matt Lane
296
asked Nov 19 '18 at 15:19
Matt Lane
296
296
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The conditional formulas essentially already follows the IF logic, they are expecting a TRUE / FALSE response:
AND(Sheet2!$B$2="Feb,$I11<(10/12))
The conditional formatting will automatically increment the cell references based on the absolute referencing
The IF Statement is null and void but if it helps you to understand, you would use it like so:
IF(AND(Sheet2!$B$2="Feb,$I11<(10/12)),TRUE,FALSE)
This will then return the TRUE / FALSE that the conditional formatting is expecting
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
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%2f53377680%2fconditional-formating-with-if-then-statement%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
The conditional formulas essentially already follows the IF logic, they are expecting a TRUE / FALSE response:
AND(Sheet2!$B$2="Feb,$I11<(10/12))
The conditional formatting will automatically increment the cell references based on the absolute referencing
The IF Statement is null and void but if it helps you to understand, you would use it like so:
IF(AND(Sheet2!$B$2="Feb,$I11<(10/12)),TRUE,FALSE)
This will then return the TRUE / FALSE that the conditional formatting is expecting
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
add a comment |
The conditional formulas essentially already follows the IF logic, they are expecting a TRUE / FALSE response:
AND(Sheet2!$B$2="Feb,$I11<(10/12))
The conditional formatting will automatically increment the cell references based on the absolute referencing
The IF Statement is null and void but if it helps you to understand, you would use it like so:
IF(AND(Sheet2!$B$2="Feb,$I11<(10/12)),TRUE,FALSE)
This will then return the TRUE / FALSE that the conditional formatting is expecting
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
add a comment |
The conditional formulas essentially already follows the IF logic, they are expecting a TRUE / FALSE response:
AND(Sheet2!$B$2="Feb,$I11<(10/12))
The conditional formatting will automatically increment the cell references based on the absolute referencing
The IF Statement is null and void but if it helps you to understand, you would use it like so:
IF(AND(Sheet2!$B$2="Feb,$I11<(10/12)),TRUE,FALSE)
This will then return the TRUE / FALSE that the conditional formatting is expecting
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
The conditional formulas essentially already follows the IF logic, they are expecting a TRUE / FALSE response:
AND(Sheet2!$B$2="Feb,$I11<(10/12))
The conditional formatting will automatically increment the cell references based on the absolute referencing
The IF Statement is null and void but if it helps you to understand, you would use it like so:
IF(AND(Sheet2!$B$2="Feb,$I11<(10/12)),TRUE,FALSE)
This will then return the TRUE / FALSE that the conditional formatting is expecting
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
answered Nov 19 '18 at 15:33
Glitch_Doctor
2,31621027
2,31621027
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
add a comment |
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
Thank you for your quick reply, Glitch_Doctor. The formula looks exactly like I am looking for. The conditional formatting takes the formula, but it doesn't turn the %'s red for me. Any ideas? Thank you, Matt
– Matt Lane
Nov 19 '18 at 16:06
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.
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%2fstackoverflow.com%2fquestions%2f53377680%2fconditional-formating-with-if-then-statement%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
