1

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my Jira. Now I am trying to compare Column F from sheet1 and sheet2 to confirm if they are the same or different. If different I need to mark that particular cell in sheet1 as GREEN. I have tried following custom formula under conditional formatting:

=("sheet1!F") <> INDIRECT("sheet2!F")

But some reason I am not sure it does not mark the respective cell as GREEN. Under conditional formatting, I am selecting cell range as F1:F1000.

Above formula should be applied to all rows in Column F even though some of the rows are hidden in sheet1 because of other filters. Is this possible in Google Sheets?

Improve this question

2 Answers 2

Reset to default
0 

=AND($F1<>INDIRECT("Sheet2!F1:F"),F1<>"")

0

demo spreadsheet

Improve this answer
0
-1 

=FILTER(F1, NOT(COUNTIF(INDIRECT("Sheet2!F1:F"), F1)))

0

demo spreadsheet

Improve this answer
5
  • Thanks user0 for the reply, but your formula really did not work in my case. Even in your example values in F4 in both the sheets are not same and still it does not highlight
    – codingfreak
    Apr 8, 2019 at 22:48
  • I am also not sure why we need FILTER and COUNTIF option here ... For e,g, I want to compare F1 in sheet1 to F1 in sheet2, if the string value is different highlight F1 in sheet1 as green.
    – codingfreak
    Apr 8, 2019 at 22:53
  • Thanks for your reply but still it is wrong. Now F1 and F2 from both the sheets are different and we are not marking them as GREEN, Similarly F5 in sheet1 is empty and sheet2 has value. This is also equivalent change in value and it is not marked GREEN.
    – codingfreak
    Apr 8, 2019 at 23:01
  • so you want it to be exact row by row?
    – user0
    Apr 8, 2019 at 23:03
  • 1
    Yes, as i was mentioning earlier my sheet2 is something which I update from Jira and my sheet1 is used for creating reports and charts. IF(row in sheet1 != sheet2 then mark it green.
    – codingfreak
    Apr 8, 2019 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.