I struggle to create a formula to highlight a cell.
I am working with two different sheets on workbook: 1.Production & 2.Stock missing
I am updating Production sheet with production order numbers (Column G). Production order is unique and can't repeat on this worksheet.
When someone have a problem with stock, they add this number to second sheet (Stock missing) in column B. They are also clarifying Location of order in column F. Location of order could be "CW" or "TP"(nothing more). When problem is sorted for the particular production order, employees are choosing "YES" from drop down list in column K (the same "Stock missing" sheet).
I want to highlight production order number in RED (in "Production" Worksheet) if the production order number equals the same production order in "Stock missing" sheet and has location of order "CW", but when they choosing "Yes" in column K, color should disappear.
If location of order is "TP" I want to do the same as above, just highlight the cell in YELLOW.
Edited version:
Try creating these conditional formatting for entire column G:G in sheet Production
(Home->Conditional Formatting->New rule->Use formula to determine which cells to format):
Use this formula and set fill to "No fill":
=IF(Production!G1="",TRUE,INDEX('Stock missing'!K:K,MATCH(Production!G1,'Stock missing'!B:B,0),1)="Yes")
Use this formula and set fill to "Red":
=INDEX('Stock missing'!F:F,MATCH(G1,'Stock missing'!B:B,0),1)="CW"
Use this formula and set fill to "Yellow":
=INDEX('Stock missing'!F:F,MATCH(G1,'Stock missing'!B:B,0),1)="TP"
Now sort them as in picture and it should be working. Order of rules
