#INVALID DATA TYPE error in formula
Hello. I have a formula which work fine for 2 out of 3 variables, but not the last and I cannot figure out what is wrong.
Use case: I want the Waiver Approval Status column to read two approval process columns and return Declined if either row shows 'declined', 'pending' if either row shows 'submitted' and 'approved' if both show approved. This is the formula:
=IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Declined"), "DECLINED", IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Submitted"), "PENDING", IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Approved"), "APPROVED", "PENDING")))
The only portion that doesn't work is if both Debbie & Lance's approval columns show 'approved' then I get the error. Every other situation works. Debbie & Lance's Approval columns are single select drop downs, the Waiver Approval Status was originally the same type (single select dropdown), but I've also changed it to Text/Number with the same result.
Best Answer
-
Try
=IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Declined") > 0, "DECLINED", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Submitted") > 0, "PENDING", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Approved") > 0, "APPROVED", "PENDING")))
Here are the various outputs
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
Answers
-
Try
=IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Declined") > 0, "DECLINED", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Submitted") > 0, "PENDING", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Approved") > 0, "APPROVED", "PENDING")))
Here are the various outputs
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
-
Great, that worked. Thank you!
-
@Natalia You're welcome!
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
Help Article Resources
Categories
Check out the Formula Handbook template!