Excel 365 Conditional Formatting based on multiple cells

0
votes

i have excel sheet with history of all items that arrived in the warehouse and delivered to two shops. The list keeps growing. I would like to set the conditional formatting as in the screenshot. here is the description of my idea:

  • Highlight (bold) the item row that was received in warehouse latest (column B). If the same item appears twice it should highlight only the latest one. Apple is in Row 3 /& Row 5. As in the screenshot row 5 should not highlighted because apple is received twice (row 3 & 5). Row 3 is highlighted since 'Date Warehouse' of Row 3 later than of Row 5.
  • cell should be highlighted (bg color orange) if date is missing in column C & D. Note that cell D5 is should not be highlighted because the row 5 for Apple is not valid (latest). for Apple row 3 is valid since it is last received based on column B.
  • as noted earlier the list keeps on growing with more data. new row should automatically get above formattings.

will appreciate your help how to specify conditional formatting.

enter image description here

1
excelconditional-formatting
You can create conditional formatting, based on a formula. for that, you might try to write formulas, giving TRUE for the cases you mention, and using those as formulas for your conditional formatting. – Dominique
@Dominique, Unfortunately i do not know how to write appropriate formulas for this. :( – user1749707

1 Answers

0
votes

I found out how to do this. i used the formula:

=MAXIFS(B2:B5,A2:A5,A2)