Highlighting Duplicates to create a Heat Map with a Formula (No VBA or Conditional Formatting please)

[16662777]

Hi All Excel Guru's,

My first post here so please accept my apologies if I am out of line.

Question: (Worksheet attached)

I have a workbook with two separate Tabs
Tab 1 - Floor Plan: Master Warehouse Map with Locations
Tab 2 - Damages : List of Damages in the warehouse

I have identified the damaged locations in Tab 2(Damages), and now I would like them to be highlighted in Tab 1 (Floor plan) using a Formula for easy visual explanation for warehouse managers.

Reason for formula and not conditional formatting is that I want to lock tab 2 with formulas so that every time I copy and paste Damage Product Data, the floor map would highlight it straightaway.

I used COUNTIF formula to match all locations =COUNTIF('Floor Plan'!$O$3:$CY$247,Damages!U2)

I appreciate your help guys and looking forward for a solution (worksheet is also attached for your reference)

PS: I am an Basic Excel user

Regards,
Saf

 

[footoo]

A formula can only return a result - it cannot change a sheet's format.