Format cells in column only if once cell equals it and another cell equals "Gold"

[kelseyRahenkamp]

I want to format cells based on the value of two other cells. I have a spreadsheet that has company names in one column with their status (silver or gold) in another column. On another sheet, I will list companies out (just certain ones, not all of them) and I want the color to change to green if their level in the other spreadsheet equals gold.

The status will change over time so I will need the second sheet to update when the status on the first sheet for that company changes.

Right now, I have this formula in my conditional formula rule: =AND(Sheet1!$A$2:$A$439=$D$4,Sheet1!$I$2:$I$439="Gold")=TRUE. Simplified, here it is: =AND("company name in sheet 1"="company name in sheet two","status in sheet 1"="Gold")=TRUE

This is not working. What do I need to do to get this to work?

 

[ExcelMercy]

Could you do something like, making a 2nd column to house the status of the company (whether silver/gold) and based on that change the color?

I tend to try to keep conditional formatting to the same sheet.

For instance on your status of the different companies sheet, where the companies and their status are listed:

Excel 2010

	A	B
1	Company Name	Status
2	Nike	Silver
3	Wal-Mart	Gold
4	Sears	Silver
5	JCPenney	Gold
6	Academy Sports	Gold
7	BassPro	Gold
8	WalGreens	Silver
9	Macys	Gold
10	Target	Silver

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Your formatted sheet.

In the cells B2:B10:
=IFERROR(INDEX(Sheet1!$B$2:$B$10,MATCH(Sheet2!A2,Sheet1!$A$2:$A$10,0)),"")

Conditional Formatting Rule:
Applies to:
=A2:A10

Format:
Fill: Green

Formula:
=B2:B10="Gold"



Excel 2010

	A	B
1	Company List:
2	Academy Sports	Gold
3	BassPro	Gold
4	Sears	Silver
5	Nike	Silver
6	JCPenney	Gold
7	Nike	Silver
8	JCPenney	Gold
9	Academy Sports	Gold
10	BassPro	Gold

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Cells A2, A3, A6, A8, A9, A10 are all green.



You could then hide Column B on Sheet2 if you don't want to see that.