IF/AND and cross-sheet reference

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.

IF the user is requesting to use the Immersive Learning Center
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)
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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…