IF/AND and cross-sheet reference

Mary Ayers

I'm trying to write an IF/AND statement that references a range of data in another Smartsheet but an #INVALID OPERATION message is returned.

1. IF the user is requesting to use the Immersive Learning Center
2. AND the user is requesting to schedule their event on a date when the Immersive Learning Center is closed (referencing list of dates in a separate Smartsheet)
3. Return: Not a valid ILC Date, Otherwise Return: Date Okay

Here's my formula:

=IF(AND([Immersive Learning Center Request?]1 = "Yes", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = {ILC Closed Dates Range 1}), "Not a valid ILC Date", "Date Okay")

What am I missing? Thanks in advance!

Jim Hook

Mary, I'm not 100% sure but I think the external reference part of your formula is trying to compare the content of one cell with an array of cells in the other sheet and that won't work. You might try the COUNTIFS() function using an external reference to see if there is a match on the other sheet that meets the conditions.

 

Mike Wilday

Well... it looks like your trying to reference a range of dates in  ILC Closed Dates Range. It looks like your reference might be messed up. 

Try this... 

=IF(AND([Immersive Learning Center Request?]1 = "Yes", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = [ILC Closed Dates Range]1), "Not a valid ILC Date", "Date Okay")

Does that work? 

Mary Ayers

Thanks Mike. Unfortunately, "#UNPARSEABLE" is returned. What if instead of referencing the range of dates that the ILC is closed via a different sheet, I include in the formula the list of dates.

I tried this but it also returned "#UNPARSEABLE" so am obviously doing something wrong -- but might be easier to resolve than the cross sheet reference:

=IF(AND([Immersive Learning Center Request?]1 = "Yes", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = "09/01/18", "09/15/18", "10/01/18"), "Not a valid ILC Date", "Date Okay")

 

Mike Wilday

=IF(AND([Immersive Learning Center Request?]1 = "Yes", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = "09/01/18", "09/15/18", "10/01/18"), "Not a valid ILC Date", "Date Okay")

In that instance, you have to use an OR statement... 

=IF(AND([Immersive Learning Center Request?]1 = "Yes", OR([Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = "09/01/18", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1= "09/15/18", [Preferred Date BETWEEN 8/27/18-8/25/19 ONLY]1 = "10/01/18"), "Not a valid ILC Date", "Date Okay")

Does that work? 

Mary Ayers

Now the message is #INCORRECT ARGUMENT

Once I added a second closing to paren to after the last date, it worked!!

Here's my final formula (I changed the name of the Preferred Date field to simplify things.

=IF(AND([Immersive Learning Center Request?]1 = "Yes", OR([Preferred Date]1 = "09/01/18", [Preferred Date]1 = "09/15/18", [Preferred Date]1 = "10/01/18")), "Not a valid ILC Date", "Date Okay")

Thanks so much!

Mary