Mixing
Excel Conditional formatting from one to many
There are 3 sheets in my workbook
A! Squad Names and base details
[ Squad # | Engaged (YES|NO) | Squad Name | Base | ]
Squad Name is highlighted when they are Engaged
B! Squad Mission(s)
[ MOS # | Squad | MOS Name | MOS Desc | ]
C! Required Data-Validation Lists for ease in Search of A!
I understand how to conditionally format of column C when column B has a specific value.
I want to Highlight the MOS Name(s) on page B! associated with specific squad names when the squad is marked as Engaged on A!.
Squad names are associated with from 5 to 17 MOS codes.
excel one-to-many conditional-formatting
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
add a comment |
There are 3 sheets in my workbook
A! Squad Names and base details
[ Squad # | Engaged (YES|NO) | Squad Name | Base | ]
Squad Name is highlighted when they are Engaged
B! Squad Mission(s)
[ MOS # | Squad | MOS Name | MOS Desc | ]
C! Required Data-Validation Lists for ease in Search of A!
I understand how to conditionally format of column C when column B has a specific value.
I want to Highlight the MOS Name(s) on page B! associated with specific squad names when the squad is marked as Engaged on A!.
Squad names are associated with from 5 to 17 MOS codes.
excel one-to-many conditional-formatting
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
add a comment |
There are 3 sheets in my workbook
A! Squad Names and base details
[ Squad # | Engaged (YES|NO) | Squad Name | Base | ]
Squad Name is highlighted when they are Engaged
B! Squad Mission(s)
[ MOS # | Squad | MOS Name | MOS Desc | ]
C! Required Data-Validation Lists for ease in Search of A!
I understand how to conditionally format of column C when column B has a specific value.
I want to Highlight the MOS Name(s) on page B! associated with specific squad names when the squad is marked as Engaged on A!.
Squad names are associated with from 5 to 17 MOS codes.
excel one-to-many conditional-formatting
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
There are 3 sheets in my workbook
A! Squad Names and base details
[ Squad # | Engaged (YES|NO) | Squad Name | Base | ]
Squad Name is highlighted when they are Engaged
B! Squad Mission(s)
[ MOS # | Squad | MOS Name | MOS Desc | ]
C! Required Data-Validation Lists for ease in Search of A!
I understand how to conditionally format of column C when column B has a specific value.
I want to Highlight the MOS Name(s) on page B! associated with specific squad names when the squad is marked as Engaged on A!.
Squad names are associated with from 5 to 17 MOS codes.
excel one-to-many conditional-formatting
excel one-to-many conditional-formatting
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
asked Nov 21 '18 at 12:02
Wolf HaltonWolf Halton
286
286
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You would have to use VLOOKUP. As suggested into the VLOOKUP Microsoft documentation, VLOOKUP is one of the lookup and reference functions, when you need to find things in a table or a range by row.
Here is how that works:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
- Value you want to look up: The cell with the MOS name to search
- Range where you want to lookup the value: From the top 1st column to the bottom 4th column of your table into the sheet "A! Squad Names and base details", e.g.:
A1:D44if there is 44 rows. - The column number in the range containing the return value: 2 as this is where we can know if this is yes or no
Then you put the VLOOKUP result into your conditional formatting. Based on Microsoft Documentation Use formulas with conditional formatting, here is the step how to dot it:
Select cells you want to apply the format on.
Then, click Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- Under Format values where this formula is true, type the formula:
=VLOOKUP(C1,'A! Squad Names and base details'!A1:D44,2,FALSE) = "YES" - Click Format and decide your preferences
- Click OK until the dialog boxes are closed.
The formatting is applied to the column selected
Hope it will help you.
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%2f53411623%2fexcel-conditional-formatting-from-one-to-many%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
You would have to use VLOOKUP. As suggested into the VLOOKUP Microsoft documentation, VLOOKUP is one of the lookup and reference functions, when you need to find things in a table or a range by row.
Here is how that works:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
- Value you want to look up: The cell with the MOS name to search
- Range where you want to lookup the value: From the top 1st column to the bottom 4th column of your table into the sheet "A! Squad Names and base details", e.g.:
A1:D44if there is 44 rows. - The column number in the range containing the return value: 2 as this is where we can know if this is yes or no
Then you put the VLOOKUP result into your conditional formatting. Based on Microsoft Documentation Use formulas with conditional formatting, here is the step how to dot it:
Select cells you want to apply the format on.
Then, click Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- Under Format values where this formula is true, type the formula:
=VLOOKUP(C1,'A! Squad Names and base details'!A1:D44,2,FALSE) = "YES" - Click Format and decide your preferences
- Click OK until the dialog boxes are closed.
The formatting is applied to the column selected
Hope it will help you.
add a comment |
You would have to use VLOOKUP. As suggested into the VLOOKUP Microsoft documentation, VLOOKUP is one of the lookup and reference functions, when you need to find things in a table or a range by row.
Here is how that works:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
- Value you want to look up: The cell with the MOS name to search
- Range where you want to lookup the value: From the top 1st column to the bottom 4th column of your table into the sheet "A! Squad Names and base details", e.g.:
A1:D44if there is 44 rows. - The column number in the range containing the return value: 2 as this is where we can know if this is yes or no
Then you put the VLOOKUP result into your conditional formatting. Based on Microsoft Documentation Use formulas with conditional formatting, here is the step how to dot it:
Select cells you want to apply the format on.
Then, click Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- Under Format values where this formula is true, type the formula:
=VLOOKUP(C1,'A! Squad Names and base details'!A1:D44,2,FALSE) = "YES" - Click Format and decide your preferences
- Click OK until the dialog boxes are closed.
The formatting is applied to the column selected
Hope it will help you.
add a comment |
You would have to use VLOOKUP. As suggested into the VLOOKUP Microsoft documentation, VLOOKUP is one of the lookup and reference functions, when you need to find things in a table or a range by row.
Here is how that works:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
- Value you want to look up: The cell with the MOS name to search
- Range where you want to lookup the value: From the top 1st column to the bottom 4th column of your table into the sheet "A! Squad Names and base details", e.g.:
A1:D44if there is 44 rows. - The column number in the range containing the return value: 2 as this is where we can know if this is yes or no
Then you put the VLOOKUP result into your conditional formatting. Based on Microsoft Documentation Use formulas with conditional formatting, here is the step how to dot it:
Select cells you want to apply the format on.
Then, click Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- Under Format values where this formula is true, type the formula:
=VLOOKUP(C1,'A! Squad Names and base details'!A1:D44,2,FALSE) = "YES" - Click Format and decide your preferences
- Click OK until the dialog boxes are closed.
The formatting is applied to the column selected
Hope it will help you.
You would have to use VLOOKUP. As suggested into the VLOOKUP Microsoft documentation, VLOOKUP is one of the lookup and reference functions, when you need to find things in a table or a range by row.
Here is how that works:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
- Value you want to look up: The cell with the MOS name to search
- Range where you want to lookup the value: From the top 1st column to the bottom 4th column of your table into the sheet "A! Squad Names and base details", e.g.:
A1:D44if there is 44 rows. - The column number in the range containing the return value: 2 as this is where we can know if this is yes or no
Then you put the VLOOKUP result into your conditional formatting. Based on Microsoft Documentation Use formulas with conditional formatting, here is the step how to dot it:
Select cells you want to apply the format on.
Then, click Home > Conditional Formatting > New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- Under Format values where this formula is true, type the formula:
=VLOOKUP(C1,'A! Squad Names and base details'!A1:D44,2,FALSE) = "YES" - Click Format and decide your preferences
- Click OK until the dialog boxes are closed.
The formatting is applied to the column selected
Hope it will help you.
answered Nov 21 '18 at 12:59
user10421833
add a comment |
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%2f53411623%2fexcel-conditional-formatting-from-one-to-many%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
