Google sheets, two-sheet dependent conditional formatting

1
votes

My problem is probably trivial ... However, I was looking for similar problems and their solutions either do not work for me or I can not apply them.

I have 2 sheets - SheetA and SheetB. They are identical. I want to select every cell in SheetA that is no longer identical to Sheet B.

I was able to use (found here on Stack Overflow) such a function in conditional formatting:

= A1 <> INDIRECT ("SheetB! A" & ROW ())

It works if the range is column A. I know that I can apply this function to each column separately, but there is certainly a way that I can apply the same formula to the whole worksheet.

Will there be anyone who can show me the correct formula?

Edit: I tried to use above formula to every column... And my file become to work very slow... So whoever wants to do the same... think again.

After some tries I decided to move everything from SheetB to SheetA. I paste it a 1000 rows under data of Sheet A. Works fine without "indirect" function. No slow downs. It is not a perfect solution but it works.

But even after my problem is solved different way, I would like to know what is the correct formula for my problem... it might be useful for the future with smaller amount of data.

Solved ;)

1
google-sheets

1 Answers

1
votes

You should be able to use the Address function to get the address of the current cell from its row and column:

=A1<>indirect("sheetB!"&address(row(),column()))

or for case-sensitive match:

=not(exact(A1,indirect("sheetB!"&address(row(),column()))))

enter image description here