Conditional formatting based on different sheets. Autofill problem
I have this file as a test: Sample File
It contains 2 sheets (1 & 2)
and two tables (A1:D5) in each sheet
What I want to do is:
To color any not empty cell from sheet1 (filled with either text or value), ONLY IF its equivalent in sheet2 is empty.
what I want to do is clear and working fine in cell A1 in sheet1
- If it's filled, and A1 in sheet2 is empty, it will be colored
- If it's filled, and A1 in sheet2 is filled too, it won't be colored
- If it's empty, it won't be colored no matter what happens in A1 in sheet2
The formula I used in the conditional formatting is:
=IF(A1<>"",ISBLANK(INDIRECT("'Sheet2'!A1")),"")
I think you understand that I've used INDIRECT because conditional formatting doesn't work across the sheets DIRECTLY.
But, in my opinion, I think the problem that this only works for cell A1 is because the reference for the cells in the formula putted in a little quotation marks. That is what prevents it from autofill to expand to affect all cells in the table.
Do you have any ideas??
use:
=LEN(A1)*ISBLANK(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,"Sheet2")))
