IF/AND and cross-sheet reference

Mary Ayers
Mary Ayers ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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!

Tags:

Comments

  • Jim Hook
    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
    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
    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
    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!