Trying to use a IF(AND formula

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


  • @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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!