Adapting Conditional Formatting Formula for Cross Sheet Reference
I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across all 3 columns. I have been using the following conditional formatting formulas =countif(A:C,A1)=1 and I have 2 more formulas with =2 and =3 at the end. It works perfectly for my needs except I would like to be able to sort/filter each column separately which Google Sheets doesn't seem capable of doing. So my thought was to separate each column into a separate sheet. The only issue is I can't figure out the proper way to make the conditional format work cross-sheet and still give me the needed results of identifying the same information 1-2-3 times spread out over 3 sheets. I tried something like =countif(Sheet1!:A,A1)=1 but it doesn't seem to be a valid formula and I'd need to check all 3 sheets, not just one. What I essentially want to do is replace the A:C part of the original formula with Sheet1:Sheet3 but I'm not sure how or if that's possible. It may not even be a possibility but figured this was the place to ask.
google-sheets formulas conditional-formatting
edited Feb 27 at 17:38
user0
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
migrated from superuser.com Feb 27 at 17:23
This question came from our site for computer enthusiasts and power users.
add a comment |
I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across all 3 columns. I have been using the following conditional formatting formulas =countif(A:C,A1)=1 and I have 2 more formulas with =2 and =3 at the end. It works perfectly for my needs except I would like to be able to sort/filter each column separately which Google Sheets doesn't seem capable of doing. So my thought was to separate each column into a separate sheet. The only issue is I can't figure out the proper way to make the conditional format work cross-sheet and still give me the needed results of identifying the same information 1-2-3 times spread out over 3 sheets. I tried something like =countif(Sheet1!:A,A1)=1 but it doesn't seem to be a valid formula and I'd need to check all 3 sheets, not just one. What I essentially want to do is replace the A:C part of the original formula with Sheet1:Sheet3 but I'm not sure how or if that's possible. It may not even be a possibility but figured this was the place to ask.
google-sheets formulas conditional-formatting
edited Feb 27 at 17:38
user0
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
migrated from superuser.com Feb 27 at 17:23
This question came from our site for computer enthusiasts and power users.
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33
add a comment |
I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across all 3 columns. I have been using the following conditional formatting formulas =countif(A:C,A1)=1 and I have 2 more formulas with =2 and =3 at the end. It works perfectly for my needs except I would like to be able to sort/filter each column separately which Google Sheets doesn't seem capable of doing. So my thought was to separate each column into a separate sheet. The only issue is I can't figure out the proper way to make the conditional format work cross-sheet and still give me the needed results of identifying the same information 1-2-3 times spread out over 3 sheets. I tried something like =countif(Sheet1!:A,A1)=1 but it doesn't seem to be a valid formula and I'd need to check all 3 sheets, not just one. What I essentially want to do is replace the A:C part of the original formula with Sheet1:Sheet3 but I'm not sure how or if that's possible. It may not even be a possibility but figured this was the place to ask.
google-sheets formulas conditional-formatting
edited Feb 27 at 17:38
user0
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across all 3 columns. I have been using the following conditional formatting formulas =countif(A:C,A1)=1 and I have 2 more formulas with =2 and =3 at the end. It works perfectly for my needs except I would like to be able to sort/filter each column separately which Google Sheets doesn't seem capable of doing. So my thought was to separate each column into a separate sheet. The only issue is I can't figure out the proper way to make the conditional format work cross-sheet and still give me the needed results of identifying the same information 1-2-3 times spread out over 3 sheets. I tried something like =countif(Sheet1!:A,A1)=1 but it doesn't seem to be a valid formula and I'd need to check all 3 sheets, not just one. What I essentially want to do is replace the A:C part of the original formula with Sheet1:Sheet3 but I'm not sure how or if that's possible. It may not even be a possibility but figured this was the place to ask.
google-sheets formulas conditional-formatting
google-sheets formulas conditional-formatting
edited Feb 27 at 17:38
user0
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
edited Feb 27 at 17:38
user0
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
edited Feb 27 at 17:38
user0
10.1k71633
edited Feb 27 at 17:38
user0
10.1k71633
edited Feb 27 at 17:38
user0
10.1k71633
10.1k71633
asked Feb 27 at 16:07
JoelJoel
82
asked Feb 27 at 16:07
JoelJoel
82
asked Feb 27 at 16:07
JoelJoel
82
82
migrated from superuser.com Feb 27 at 17:23
This question came from our site for computer enthusiasts and power users.
migrated from superuser.com Feb 27 at 17:23
This question came from our site for computer enthusiasts and power users.
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33
add a comment |
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33
add a comment |
1 Answer
1
active
oldest
votes
conditional formatting can't intake a cross-sheet reference without
INDIRECTtherefore try:
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
and from there you can build an array:
=COUNTIF({INDIRECT("Sheet1!A1:A");
INDIRECT("Sheet2!A1:A");
INDIRECT("Sheet3!A1:A")}, A1)=1
answered Feb 27 at 17:30
user0user0
10.1k71633
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
add a comment |
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "34"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
},
noCode: 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%2fwebapps.stackexchange.com%2fquestions%2f125803%2fadapting-conditional-formatting-formula-for-cross-sheet-reference%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
conditional formatting can't intake a cross-sheet reference without
INDIRECTtherefore try:
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
and from there you can build an array:
=COUNTIF({INDIRECT("Sheet1!A1:A");
INDIRECT("Sheet2!A1:A");
INDIRECT("Sheet3!A1:A")}, A1)=1
answered Feb 27 at 17:30
user0user0
10.1k71633
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
add a comment |
conditional formatting can't intake a cross-sheet reference without
INDIRECTtherefore try:
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
and from there you can build an array:
=COUNTIF({INDIRECT("Sheet1!A1:A");
INDIRECT("Sheet2!A1:A");
INDIRECT("Sheet3!A1:A")}, A1)=1
answered Feb 27 at 17:30
user0user0
10.1k71633
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
add a comment |
conditional formatting can't intake a cross-sheet reference without
INDIRECTtherefore try:
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
and from there you can build an array:
=COUNTIF({INDIRECT("Sheet1!A1:A");
INDIRECT("Sheet2!A1:A");
INDIRECT("Sheet3!A1:A")}, A1)=1
answered Feb 27 at 17:30
user0user0
10.1k71633
conditional formatting can't intake a cross-sheet reference without
INDIRECTtherefore try:
=COUNTIF(INDIRECT("Sheet1!A1:A"), A1)=1
and from there you can build an array:
=COUNTIF({INDIRECT("Sheet1!A1:A");
INDIRECT("Sheet2!A1:A");
INDIRECT("Sheet3!A1:A")}, A1)=1
answered Feb 27 at 17:30
user0user0
10.1k71633
edited Feb 27 at 17:38
answered Feb 27 at 17:30
user0user0
10.1k71633
answered Feb 27 at 17:30
user0user0
10.1k71633
answered Feb 27 at 17:30
user0user0
10.1k71633
10.1k71633
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
add a comment |
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
This worked perfectly. Thank you so much! Never would've figured that out lol
– Joel
Feb 27 at 19:23
add a comment |
Thanks for contributing an answer to Web Applications Stack Exchange!
- 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%2fwebapps.stackexchange.com%2fquestions%2f125803%2fadapting-conditional-formatting-formula-for-cross-sheet-reference%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
Related :Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet
– Rubén
Feb 27 at 17:33