Trying to use a IF(AND formula

System

This discussion was created from comments split from: If Function between different sheets.

Bianca Mitchell

@Paul Newcome, I am hoping you might be able to help me... I am trying to use a IF(AND formula but I keep getting an error '#invalid data type'. I am trying to create a dashboard that on one side shows a booking form and the other side shows a calendar with availability... I have followed another post where you helped out which is where I got the idea of doing this from.

So far I have...

One sheet which is used as the 'Workspace Booking Form' which captures the relevant data; name, email, date, workspace..

Then I have another sheet which has all of the workspaces and dates listed and a 'booked' column which I was hoping to reference the booking form to show if it was booked or not =IF(AND{Workspace Booking Form - Date}, Date1, {Workspace Booking Form - Workspace}, Workspace1), "Booked", "Available") I keep getting the invalid data type error though?

Once I work out the formula glitch I was hoping to pull the data from the booked column and show that in the primary column + the workspace so in calendar view you can see what is available.

Does that make sense? Would love any help or advice you might have...

Here is a snippit of the workspace booking sheet (pulled through the form)

And this is the meeting room sheet that I am having issues with the formula...

David Joyeuse

Hi @Bianca Mitchell

There's a problem with your AND formula as it's not written correctly, and smartsheet won't be able to check if a range is equal to a specific value here. I haven't checked Paul's solutions about it, but in my understanding of what you want to achieve, some INDEX/COLLECT should do the trick.

=IF(INDEX(COLLECT({Workspace Booking Form - Name}, {Workspace Booking Form - Date}, Date@row, {Workspace Booking Form - Workspace}, Workspace@row),1)<>"", "Booked", "Avialable")

This should return a booked each time the Collect function grabbed something in the name column.

Hope it helped!