Referencing an entire column from another sheet


I am working on a cross-sheet formula. The ultimate goal is to write something that says this:

If room column on [Sheet 2] contains "room name" AND (date on [Sheet 1] falls between Check in Date and Check Out Date on [Sheet 2]) return the value "reserved," if not leave blank.

The trouble I am having is referencing an entire column from the other sheet (Sheet 2). Here is what I have so far:

=IF(AND({All Requests Range 4} = "Mountain Oaks 101", Date7 >= {All Requests Range 12}, Date7 <= {All Requests Range 13}), "reserved", " ")

The "{All Requests Range 4}" is looking at just one cell on Sheet 2 and I need it to look at an entire range. When I select the entire column or even a range of data on the other sheet I get an error message "#INVALID OPERATION." How can I improve my formula to give me the result I'm looking for?

Thanks in advance,


Best Answer


  • Hi Genevieve,

    THANK YOU THANK YOU THANK YOU! This is what I've been missing! Your formula worked perfectly.

    I so appreciate your help!

    Have a wonderful day and a great week!



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Tayler,

    That's so great to hear! I'm glad it worked for you. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!