Google-sheets – Conditionally format cells that not match with range from Sheet2

conditional formattingformulasgoogle sheets

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?

Best Answer

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

demo spreadsheet

Related Solutions

Google-sheets – Identifying and working with any cells that match

I'd suggest a pivot table and for this it may be a good idea to label your Original Table (say Fruit in A4 and Qty in B4) though not essential. Then select from A4 to as far down in B as you are likely to require (but can be changed later if still insufficient) and Data, Pivot table report..., click on - Add field next to Rows and select Fruit, click on - Add field next to Values and select Qty.

You can copy the resulting table back in to the original sheet if you wish.

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Best Answer

Related Solutions

Google-sheets – Identifying and working with any cells that match

Google-sheets – How to check if a cell is blank for Conditional formatting

Related Topic