Looking for a formular to help with a rooming list...

PhilWatTravelPlaces

I have lots of passengers on separate rows.

I have a heading row of dates.

I then have a sequence of 1's in a grid that show which nights a person will check in for 1 over night stay.

In the first example, my traveller will check in on 17/06 and check out on 24/06.

I am currently having to update the columns manually when somebody tags me in a comment to say the grid has chnaged.

I wish to have a column called check in and check out, which auto updates whenever 1's in the grid are updated... please help :)

jmyzk_cloudsmart_jp

Hi @PhilWatTravelPlaces

Using your idea of "MATCH FUNCTION," colum38, I created formulas for check-in and check-out.

MATCH

=MATCH(1, [TUESDAY 11/06]1:[MONDAY 24/06]1)

Total Nights

=COUNTIF([TUESDAY 11/06]@row:[MONDAY 24/06]@row, 1)

Check In

=DATE(2024, 6, 10) + MATCH@row

Check out

=[Check In]@row + [Total Night]@row

https://app.smartsheet.com/b/publish?EQBCT=e6d625b8abc648a594898e5b1d12dea9

However, I found your system hard to maintain, as you need a new sheet as the columns like TUESDAY 11/06 get changed.

I would use Smartsheet's Gannt View to show which days are already reserved and let the user edit the Check In and Check Out columns, as shown in the image below.

The formula for Hotel & Room Type & Nights is as follows, whose value is shown in the gannt chart.

=Hotel@row + " - " + [Room Type]@row + " " + ([Check Out]@row - [Check In]@row) + "nights"

https://app.smartsheet.com/b/publish?EQBCT=143ed1021fc44d4cb9798132e8fa1ec8