Formating Question with multiple criteria

Joey_Ng

New Member
Joined
Mar 7, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.


Thank you

Joey
 

Attachments

  • Capture.PNG
    Capture.PNG
    48.6 KB · Views: 6

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
For the formatting select D3:O6 & use this formula
Excel 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

New Member
Joined
Mar 7, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

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

Many Thanks,

Joey
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,808
Messages
5,574,434
Members
412,592
Latest member
moonsugar
Top