0

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??

player0
  • 124,011
  • 12
  • 67
  • 124
vic2pal
  • 11
  • 3
  • Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables makes **it is easier to copy/paste**. If you share spreadsheets/images as the only source of data, your question may be closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 28 '22 at 14:09
  • See [if this explanation helps](https://stackoverflow.com/a/69635326/166452) – TheMaster Oct 28 '22 at 14:20

1 Answers1

0

use:

=LEN(A1)*ISBLANK(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,"Sheet2")))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124