Check room availability for same date.

Hello Smartfamily !

I have a sheet where I need to check if a room is available for the same date.

The screenshots below shows how the column "Availability" SHOULD behave.

-----

For the date April 24 2023, I select Room 1. Room 1 is not already selected for that date so "Availability"@row shows a checkmark

As soon as I select Room 1 again for the same date, "Availability" shows a X so I can easily see that it's already been selected.

-----

What is the formula I should have in the "Availability" column?

Thank you ! Keep being Smart !

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

ShelbyWarren

@Francis PJ P. Is this data all kept in one sheet or will you be referencing another sheet that contains the room numbers?

Samuel Mueller

@Francis PJ P. try something like this

=if(countifs(room:room, room@row, date:date, date@row)>1, "No", "Yes")

Francis PJ P.

https://community.smartsheet.com/discussion/comment/373060#Comment_373060

All in one sheet. :)

Francis PJ P.

https://community.smartsheet.com/discussion/comment/373061#Comment_373061

This works ! Thank you!

I have "upgraded" your suggestion a bit so that if no room has been selected, the Availability row does not show a green checkmark.

=IF(Room@row = ""; ""; IF(COUNTIFS(Room:Room; Room@row; Date:Date; Date@row) > 1; "No"; "Yes"))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.