How can I adjust my formula to return a value from another sheet?

Tayler Schlottman

Hello,

I am trying to adjust the formula below to return a value from a cell on another sheet if the room number and dates match.

Here is the formula currently:

=IF(COUNTIFS({All Requests Range 2}, "Beaumont 111", {All Requests Range 5}, <=Date@row, {All Requests Range 6}, >=Date@row) = 1, "reserved", " ")

This formula works great, but I want to adjust it so that instead of it saying "reserved," it fills that space with the name of the person who booked the room.

I have tried referencing the sheet's column but I get "#INVALID COLUMN DATA." See below.

=IF(COUNTIFS({All Requests Range 2}, "Beaumont 111", {All Requests Range 5}, <=Date@row, {All Requests Range 6}, >=Date@row) = 1, {Manage Current Reservations (Admin Only) Range 2}, " ")

Any ideas on how I can adjust the formula to show the name of the person who has that room reserved for a specific date?

Thank you,

Tayler

Any ideas on how I can return the name of the person reserving "Beaumont 111" if the dates from sheet the sheet are within the da

Kelly Moore

Hi Tayler,

As I try to interpret your cross sheet references - are you referencing one sheet or two (All Requests and Manage Current Reservations), that is, are you trying to pull data from two different source sheets into whatever the target sheet is where your formula will reside? I'm having to assume the criteria you selected will filter your data.

=IFERROR(JOIN(COLLECT({Manage Current Reservations (Admin Only) Range 2}, {All Requests Range 2}, "Beaumont 111", {All Requests Range 5}, <=Date@row, {All Requests Range 6}, >=Date@row)), "")

@Genevieve P gave an excellent explanation of the formula here

Tayler Schlottman

Oh gosh - sorry! I just realized how confusing that was. 🤦

All requests is the same as manage current reservations. I recently updated the name of the sheet. So to answer your question, there is only one sheet here that I am referencing. Here is what the formula should look like:

=IF(COUNTIFS({Manage Current Reservations Range 2}, "Beaumont 111", {All Requests Range 5}, <=Date@row, {All Requests Range 6}, >=Date@row) = 1, {Manage Current Reservations Range 3}, " ")

Thank you!

Tayler Schlottman

Hi @KDM I am just following up on this.

Kelly Moore

Hi Tayler.

Sorry - I wondered if the alternate formula I offered had worked.

Tayler Schlottman

@KDM So sorry - I was thinking the formula was meant for two sheets! Just gave it a shot and it worked like a CHARM! Thank you so so much!

Have a great week!

Tayler