How do I format cells in a range based on multiple criteria from a table in Google Sheets?

Question

I am trying to make a shared Google Sheet for the Motel I work at where employees can do a cleanliness check on a room, enter the room number and date into a table, and have the 'Room Check Status' in columns H:K update automatically.

I would like cells in the range H3:K18 to have a black background if they appear in the editable 'Out of Order' list. I would like the remaining rooms to appear red if they have not been checked in more than 14 days, yellow if they have been checked between 8-14 days ago, and green if they have been checked in the past week.

The formula(s) would need to check to see if the Room Number appears in the table and if not, change the cell in the 'Status' range red. If the room does appear in the table, it would then check the Date column to see how many days ago the check was performed, and color the 'Status' cell accordingly.

Here is the format I'm working with

For anyone concerned about cleanliness, the rooms are cleaned regularly, this is more a check on the condition of furniture, paint, appliances, etc.!

I tried setting up 3 conditional formatting rules for the Status range:

(Green) =and(eq(vlookup(H3, A:A, 1, false), H3), (D:D<=today()-7))

(Yellow) =and(eq(vlookup(H3, A:A, 1, false), H3), (D:D>today()-7), (D:D<=today()-14))

(red) =and(eq(vlookup(H3, A:A, 1, false), H3), (D:D>today()-14))

This resulted in 118, 122, 138 turning Green and 110, 130 turning Red. Of these highlights, only rooms 118 and 138 are correct.

I also tried using similar formulas with FLATTEN() applied to the Status range, but no success.

Any help would be greatly appreciated!

Would you please edit your question to include sample data (pref. as text) AND an example of the expected successful outcome. — Tedinoz
– Tedinoz, Commented Jul 26, 2023 at 6:38

Tedinoz · Accepted Answer · 2023-07-26 07:35:03Z

0

How's this. They are all custom formulas.

Out of Order

=match(H3,$M$3:$M$18,0)>=1

Greater than 14 days ago

=gt(today()-vlookup(H3,$A$3:$D$8,4,0),14)

Between 8 & 14 days

=isbetween(today()-vlookup(H3,$A$3:$D$8,4,0),8,14,true,true)

Between 1 & 7 days

=isbetween(today()-vlookup(H3,$A$3:$D$8,4,0),1,7,true,true)

Outcome

answered Jul 26, 2023 at 7:35

Tedinoz

8,3943 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Miko Caso Over a year ago

This was a BIG step in the right direction, so thank you! I adjusted the range in VLOOKUP to

Miko Caso Over a year ago

This was a BIG step in the right direction, so thank you! I adjusted the range in VLOOKUP to $D:$D so that it would continue to function as more items were added. I ran into an issue where re-checks of the same rooms on later lines would not update the Status, as VLOOKUP would stop once it found the first matching value in Column A. There was probably a much easier way to do this, but I used a SORT function in some unused columns to make a flipped copy of A:D and made the conditional formatting use that data instead. Now it's all working! Thanks again @Tedinoz

Collectives™ on Stack Overflow

How do I format cells in a range based on multiple criteria from a table in Google Sheets?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related