Referencing an entire column from another sheet

Tayler Schlottman

Hello,

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,

Tayler

Genevieve P.

Hi @Tayler Schlottman

Instead of just an IF statement, you'll want to use something like a COUNTIFS statement within an IF statement for your cross-sheet formula. This will help it look at the entire column but search for one specific criteria.

We could say look to see if there is a COUNT of 1 row that meets all these criteria. If there is, then return "Reserved". If there are 0 rows that meet this criteria, then be blank. (Keep in mind there is nothing here that says what to do if there are 2 rows or more that meet the criteria).

The way a COUNTIFS works is that first you list the range, then a comma, then the criteria.

Try this:

=IF(COUNTIFS({All Requests Range 4}, "Mountain Oaks 101", {All Requests Range 12}, <=Date@row, {All Requests Range 13}, >= Date@row) = 1, "reserved", " ")

If you have all of your Room Names listed out in this current sheet, you could replace the room name currently listed "in quotes" with the cell reference and @row, instead.

Let me know if you have any questions about this!

Cheers,

Genevieve