Conditional Formatting Question

[Gary]

Hi All,

How do I do the following?

If A1 in Sheet1 = A or B or C or D and A1 in Sheet2 = X then A1 in Sheet1
should be red. but if A1 in Sheet1 = E and A1 in Sheet2 = Y then A1 in
Sheet1 should be red.

Thanks
Gary

 

[PCLIVE]

You can not reference other worksheets in conditional formatting.
You may use the formula to return your result first and then perform
conditional formatting on that cell.

=IF(AND(OR(Sheet1!A1="A",Sheet1!A1="B",Sheet1!A1="C",Sheet1!A1="D"),Sheet2!A1="X"),"Match","No
Match")

HTH,
Paul

 

[krcowen]

Gary

You can't base conditional formatting in one worksheet on a value in
another worksheet unless, you are basing it on a named range. If you
give cell A1 in Sheet2 a name, say "test", then apply the following
two conditionsfto the ormat to cell A1 in Sheet1:

=AND(OR($A$1="A",$A$1="B",$A$1="C",$A$1="D"),test="X")
=AND($A$1="E",test="Y")

I think you should get what you want. You can probably simpify the
one with the OR a little bit.

Good luck.

Ken
Norfolk, Va

 

[Gary]

Ho do I name a cell?

Gary

You can't base conditional formatting in one worksheet on a value in
another worksheet unless, you are basing it on a named range. If you
give cell A1 in Sheet2 a name, say "test", then apply the following
two conditionsfto the ormat to cell A1 in Sheet1:

=AND(OR($A$1="A",$A$1="B",$A$1="C",$A$1="D"),test="X")
=AND($A$1="E",test="Y")

I think you should get what you want. You can probably simpify the
one with the OR a little bit.

Good luck.

Ken
Norfolk, Va

 

[krcowen]

Select the cell, then click Insert then Name, then define, then type
in the name you want. If you name cell A1 of sheet2 "test", then you
can type in "test" in a formula, or conditional format condition, or
pretty much anywhere you would previously have had sheet2!a1 and it
will be pretty much the same.

 

[Guest]

In Sheet 2
A1: =IF(OR(Sheet1!A1={"A","B","C","D"}),"X",IF(Sheet1!A1="E","Y",""))

In Sheet 1
select A1
Conditional Formatting
Formula Is: =OR($A$1="A",$A$1="B",$A$1="C",$A$1="D",$A$1="E")
format cell as Red

 

[Gary]

Thanks a ton Ken.

Select the cell, then click Insert then Name, then define, then type
in the name you want. If you name cell A1 of sheet2 "test", then you
can type in "test" in a formula, or conditional format condition, or
pretty much anywhere you would previously have had sheet2!a1 and it
will be pretty much the same.