#INVALID OPERATION in my nested IF statement

Rebekah Valdez-Pound

Hi there Community!

#INVALID OPERATION has me beat today. I believe the issue is somewhere in the bold area bc when I pull that section out I don't get the "#INVALID OPERATION" error message. The error is only displays when the row meets the criteria of the last IF statement ("Occupied"). When the logic is met for the bold items, the formula returns the anticipated result.

I'm sure it's just sitting there right in front of me, but I've been looking at it for too long. Fresh eyes greatly appreciated! Thank you!

---------------------------------------------------------------------------------------------------------

=IF([Hold Type]@row <> "","HOLD",

 IF(AND([2ND CHANCE COE (263)]@row <> "",[1ST CK RCVD (017)]@row <> ""),"2nd Ch. CWCOT",

 IF(AND([2ND CHANCE COE (263)]@row = "",[1ST CK RCVD (017)]@row <> ""),"3PS CWCOT",

 IF([CONVEY DEED RECORDED (041)]@row <> "","Convey",

 IF(AND([ICC (039)]@row <> "",[CONVEY DEED RECORDED (041)]@row = ""),"ICC",

 IF(AND([FTV (012)]@row = "",[UNLAWFUL DETAINER (011)]@row <> ""),"Eviction",

 IF(AND([UNLAWFUL DETAINER (011)]@row <> "",[EVICTION COMPLETED (177)]@row = ""),"Eviction- Vac",

 IF(AND([FTV (012)]@row <> "",[C&S ORDERED (062)]@row <> ""),"Prop Pres",

 IF(AND([Confirmation Needed?]@row = "Yes",[Redemption Needed?]@row = "Yes", [FTV (012)]@row <> "", MAX([SALE CONFIRMATION NEEDED (119)]@row,[POST SALE REDEMPTION EXPIRES (353)]@row) >= TODAY()),"Redemption- Vac",

 IF(AND([Confirmation Needed?]@row = "Yes",[Redemption Needed?]@row = "No" , [FTV (012)]@row <> "", [SALE CONFIRMATION NEEDED (119)]@row >= TODAY()),"Redemption- Vac",

 IF(AND([Confirmation Needed?]@row = "No" ,[Redemption Needed?]@row = "Yes", [FTV (012)]@row <> "", [POST SALE REDEMPTION EXPIRES (353)]@row >= TODAY()),"Redemption- Vac",

 IF(AND([Confirmation Needed?]@row = "Yes",[Redemption Needed?]@row = "Yes", [FTV (012)]@row = "", MAX([SALE CONFIRMATION NEEDED (119)]@row,[POST SALE REDEMPTION EXPIRES (353)]@row) >= TODAY()),"Redemption- Occ",

 IF(AND([Confirmation Needed?]@row = "Yes",[Redemption Needed?]@row = "No" , [FTV (012)]@row = "", [SALE CONFIRMATION NEEDED (119)]@row >= TODAY()),"Redemption- Occ",

 IF(AND([Confirmation Needed?]@row = "No" ,[Redemption Needed?]@row = "Yes", [FTV (012)]@row = "", [POST SALE REDEMPTION EXPIRES (353)]@row >= TODAY()),"Redemption- Occ",

 IF([UNLAWFUL DETAINER (011)]@row = "","Occupied","Status Error")))))))))))))))

Rebekah Valdez-Pound

Invalid Operations Issue- Smartsheet 20191223.png

Smartsheet formula referenced in question (slightly easier to read).

Andrée Starå

Hi Rebekah,

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Rebekah Valdez-Pound

Good morning Andrée,

I created a copy of the sheet, purged the NPI and shared it with you.

Greatly appreciate your help!

Andrée Starå

Excellent!

I'll take a look and get back to you!