Formating Question with multiple criteria

[Joey_Ng]

Hi,

Link below contains spreadsheet with 3 tabs: Toy, Games, Ref. The icon sets has value 1 = tick, 2 = exclamation mark, 3 = cross.

What I would like to achieve:
- Toy tab: create a formatting rule for columns D to O to look up the Name (eg, A3), Code (eg, B3), weeknum (eg, D1) in the "Ref" tab for exact match. Once match found, if the week number in "Toy" tab is a 3 (which is the cross) then format by filling the cell with "blue color". Do nothing if the cell doesn't contain a 3 (which is a cross). For example, Code 1111 shows week 37, 38, 46, 47 in the Ref tab and in the Toy tab, only week 47 has a 3 (cross) so I like cell M3 to fill be filled with blue color. Similarly, code 1112 for Toy in the Ref tab shows week 37-51 and in the Toy tab, weeks 43,45,47 has a 3 (cross) so these 3 cells I4, K4, M4 to be filled with blue.
- Games tab: similar to above, I would like the same formatting to be applied. For example, code 1113 in Ref tab shows week 39, 42 for Games and in the Games tab, week 46 (cell L5) has a 3 (cross) so I would like this cell to be formatted in blue.
- Column P (Clash Y/N) in both Toy and Games tabs, if there is a blue filled in any cells for each row, shows Y. Otherwise, we can use the above comparison for the two tabs and if they clash with a 3 (cross) for each row, change to Y.

Any help would be much appreciated. I am using office365.

Formatting Question.xlsm

drive.google.com

Thank you

Joey

 

[Fluff]

For the formatting select D3:O6 & use this formula

=AND(D3=3,XMATCH($A3&"|"&$B3&"|"&D$1,Ref!$A$2:$A$37&"|"&Ref!$B$2:$B$37&"|"&Ref!$C$2:$C$37,0))

 

[Joey_Ng]

Hi Fluff,

Thank you very much. I don't have Xmatch but using Match works fine.

Many Thanks,

Joey

 

[Fluff]

You're welcome & thanks for the feedback.