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

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

Tayler Schlottman

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!

Best,

Tayler

Genevieve P.

Hi Tayler,

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