Conditional Formatting based on 3 criteria in same row

[burnsy180]

Hi all,
I'm working on a sheet that is basically for accommodation room allocation. Simply put, I want a room present on the table to colour the corresponding room in the diagram RED. ie, the RED room is booked.

The sheet has about 3 or so different buildings on the sheet, say 111, 222 and 333, each with 3 floors (Ground, Middle, Top), and each floor with about 20 rooms.

I have 3 columns in the table (BLD, Floor, ROOM), so I have been trying to create a conditional formatting formula to check the table for the 3 criteria in the same row (eg. BLD 111, Ground, ROOM 15).

I've tried using IF(3 x MATCH formulas are on the same row), which works, but it will only find the first occurence of the BLD, not recurring ones.

So if the first booking is 111,GND,15, and the second is 111, GND, 16, the second formula won't work because the first occurence of 111 isn't on the same row as the "16".

I'm stumped at the moment and could really use some help.

Sample sheet can be provided, for some reason I can't upload it at the moment

THANK YOU!

 

[burnsy180]

Sample below.
To reiterate, I just want the corresponding room to go RED when it's on the table on the right.

PLEASE HELP.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Hence my abbreviated sample below.

See if you can adapt this to your ranges.

It would make this simpler if you labelled the floor rows as shown below. If you don't want the look of that you could then format the text in those column A cells to match the cell background colour (white?)

20 04 11.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3								BLK	ROOM	FLOOR
4		111						111	4	Ground
5	Ground	1	3	5				222	1	Top
6	Ground	2	4	6				111	6	Top
7	Middle	1	3	5
8	Middle	2	4	6
9	Top	1	3	5
10	Top	2	4	6
11
12
13
14
15		222
16	Ground	1	3	5
17	Ground	2	4	6
18	Middle	1	3	5
19	Middle	2	4	6
20	Top	1	3	5
21	Top	2	4	6
22
CF Room

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:D10	Expression	=COUNTIFS($H$4:$H$6,$B$4,$I$4:$I$6,B5,$J$4:$J$6,$A5)	text	NO
B16:D21	Expression	=COUNTIFS($H$4:$H$6,$B$15,$I$4:$I$6,B16,$J$4:$J$6,$A16)	text	NO

 

[burnsy180]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Hence my abbreviated sample below.

See if you can adapt this to your ranges.

It would make this simpler if you labelled the floor rows as shown below. If you don't want the look of that you could then format the text in those column A cells to match the cell background colour (white?)

20 04 11.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3								BLK	ROOM	FLOOR
4		111						111	4	Ground
5	Ground	1	3	5				222	1	Top
6	Ground	2	4	6				111	6	Top
7	Middle	1	3	5
8	Middle	2	4	6
9	Top	1	3	5
10	Top	2	4	6
11
12
13
14
15		222
16	Ground	1	3	5
17	Ground	2	4	6
18	Middle	1	3	5
19	Middle	2	4	6
20	Top	1	3	5
21	Top	2	4	6
22
CF Room

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:D10	Expression	=COUNTIFS($H$4:$H$6,$B$4,$I$4:$I$6,B5,$J$4:$J$6,$A5)	text	NO
B16:D21	Expression	=COUNTIFS($H$4:$H$6,$B$15,$I$4:$I$6,B16,$J$4:$J$6,$A16)	text	NO

Thank you so much.

I've adapted this to my sheet and it's working perfectly!​

So from my understanding, the COUNTIFS will return 1 if all the criteria are met. And i'm guessing 1=TRUE as far as the conditional formatting is concerned.

What I don't understand is how does the formula know all criteria are on the same row? I would've thought that if it found the any of the criteria (ie.BLK) anywhere in their respective columns, then it would be true?

Also thanks for the advice re profile details, will update shortly. Also, I am using a work computer so I'm unable to install the XL2BB add-on on this PC. I will however install that on my home machine for further assistance requests.

 

[Peter_SSs]

I've adapted this to my sheet and it's working perfectly!

Good news!

the COUNTIFS will return 1 if all the criteria are met. And i'm guessing 1=TRUE as far as the conditional formatting is concerned.

COUNTIFS could return a number greater than 1 but in your case that would only happen if there was 1 or more duplicate row(s) in the BLK/ROOM/FLOOR table which I guess is unlikely for you. However if it did happen it would still mean the room is booked and in Conditional Formatting zero is counted as False and any other number is counted as True so it would still be highlighted.

What I don't understand is how does the formula know all criteria are on the same row?

That's just how COUNTIFS works, it does a row-by-row check of the conditions and only increases the count if all conditions for that row are met. Video & pretty good written info about it here.

I'm unable to install the XL2BB add-on on this PC

It isn't nearly as good, but for sample values (no CF, formulas row/column labels etc), you can just copy/paste directly from Excel

BLK	ROOM	FLOOR
111​	4​	Ground
222​	1​	Top
111​	6​	Top