Format blank cells based on text in another cell
I would like to apply conditional formatting to a sheet, however, cant figure out the right formula.
The formatting would only apply to BLANK cells in columns S to AT based on text in in column I.
so if I1 = "live", any BLANK cells in rows S to AT need to be highlighted in yellow to draw attention.
This needs to be applied to the whole sheet which is about 1000 rows
microsoft-excel worksheet-function formatting conditional-formatting
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
add a comment |
I would like to apply conditional formatting to a sheet, however, cant figure out the right formula.
The formatting would only apply to BLANK cells in columns S to AT based on text in in column I.
so if I1 = "live", any BLANK cells in rows S to AT need to be highlighted in yellow to draw attention.
This needs to be applied to the whole sheet which is about 1000 rows
microsoft-excel worksheet-function formatting conditional-formatting
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03
add a comment |
I would like to apply conditional formatting to a sheet, however, cant figure out the right formula.
The formatting would only apply to BLANK cells in columns S to AT based on text in in column I.
so if I1 = "live", any BLANK cells in rows S to AT need to be highlighted in yellow to draw attention.
This needs to be applied to the whole sheet which is about 1000 rows
microsoft-excel worksheet-function formatting conditional-formatting
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
I would like to apply conditional formatting to a sheet, however, cant figure out the right formula.
The formatting would only apply to BLANK cells in columns S to AT based on text in in column I.
so if I1 = "live", any BLANK cells in rows S to AT need to be highlighted in yellow to draw attention.
This needs to be applied to the whole sheet which is about 1000 rows
microsoft-excel worksheet-function formatting conditional-formatting
microsoft-excel worksheet-function formatting conditional-formatting
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
asked Jan 31 at 12:25
Megan LombardMegan Lombard
1
1
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03
add a comment |
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03
add a comment |
1 Answer
1
active
oldest
votes
Start by selection S1 and chosing to create a new Consitional Formatting rule then chose to base it on a formula. The formula you want is
=IF(AND(S1="",$I1="live"),TRUE,FALSE)
This works by always looking in Column I for "live" and then looking to see if S1 is blank. By having S1 without any $ symbols is is fully dynamic, whilst the reference to column I is static for the column but dynamic for the row.
You can then set your formatting choices before applying setting to rule to apply to the entire area required.
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
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%2f1400533%2fformat-blank-cells-based-on-text-in-another-cell%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
Start by selection S1 and chosing to create a new Consitional Formatting rule then chose to base it on a formula. The formula you want is
=IF(AND(S1="",$I1="live"),TRUE,FALSE)
This works by always looking in Column I for "live" and then looking to see if S1 is blank. By having S1 without any $ symbols is is fully dynamic, whilst the reference to column I is static for the column but dynamic for the row.
You can then set your formatting choices before applying setting to rule to apply to the entire area required.
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
add a comment |
Start by selection S1 and chosing to create a new Consitional Formatting rule then chose to base it on a formula. The formula you want is
=IF(AND(S1="",$I1="live"),TRUE,FALSE)
This works by always looking in Column I for "live" and then looking to see if S1 is blank. By having S1 without any $ symbols is is fully dynamic, whilst the reference to column I is static for the column but dynamic for the row.
You can then set your formatting choices before applying setting to rule to apply to the entire area required.
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
add a comment |
Start by selection S1 and chosing to create a new Consitional Formatting rule then chose to base it on a formula. The formula you want is
=IF(AND(S1="",$I1="live"),TRUE,FALSE)
This works by always looking in Column I for "live" and then looking to see if S1 is blank. By having S1 without any $ symbols is is fully dynamic, whilst the reference to column I is static for the column but dynamic for the row.
You can then set your formatting choices before applying setting to rule to apply to the entire area required.
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
Start by selection S1 and chosing to create a new Consitional Formatting rule then chose to base it on a formula. The formula you want is
=IF(AND(S1="",$I1="live"),TRUE,FALSE)
This works by always looking in Column I for "live" and then looking to see if S1 is blank. By having S1 without any $ symbols is is fully dynamic, whilst the reference to column I is static for the column but dynamic for the row.
You can then set your formatting choices before applying setting to rule to apply to the entire area required.
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
answered Jan 31 at 12:41
RickyTillsonRickyTillson
32718
32718
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%2f1400533%2fformat-blank-cells-based-on-text-in-another-cell%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
S to AT are columns not rows. You refer to them as rows in the 3rd paragraph
– Forward Ed
Jan 31 at 13:03