Nested IF formula resulting in #INVALID DATA TYPE
I am working on a formula to indicate whether a vendor is approved for work and needed to add another condition and a new status. It is working for the most part, but a few rows are pulling #invaliddatatype and I cannot see how to fix it.
What I want the formula to say: If the [Agreement Expiration Date] was last year and the [Ins Status] is Current or N/A = "YES - Update Pending", if the [Agreement Type] is MBPO, Truck, or BPO and the [Ins Status] is Current or N/A ="YES", if the [Ins Status] is Current or N/A and [Agreement EXE] is checked = "YES", if [Ins Status] is Contact Natalia = "Contact Natalia", otherwise "No".
I have gotten the below formula to work 95% of the time, but it seems to pull the #invalid data type error for any row that has a blank in Agreement Expiration Date.
=IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO"))))
Basically what I need in those rows with errors is any that have Ins Status of Contact Natalia, to have App'd for Work also = Contact Natalia, and those with other Ins Status = NO
Best Answer
-
Try wrapping the whole thing in an IFERROR to set the value to "NO" if there's an error, since any row with a blank date value will be unable to produce a valid value in YEAR([Agreement Expiration Date]@row). See additions to your formula in BOLD.
=IFERROR(IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO")))), "NO")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Try wrapping the whole thing in an IFERROR to set the value to "NO" if there's an error, since any row with a blank date value will be unable to produce a valid value in YEAR([Agreement Expiration Date]@row). See additions to your formula in BOLD.
=IFERROR(IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO")))), "NO")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Perfect, thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!