Using IF and OR to Assign a Contact
I am currently using the following:
=IF(Line@row = "Apples", {Contact 1}), IF(Line@row = "Oranges", {Contact 2}), IF(Line@row = "Pineapples", {Contact 3}), IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}), IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}), IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}), IF(Line@row = "Strawberries", {Contact 7}), IF(Line@row = "Pickles", {Contact 8}), IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9}))
I want to assign the contact to a cell based off of a form entry (the fruit or vegetables). Is there something that you see that I'm doing wrong? I am referencing that contact from another sheet. My error is #UNPARSEABLE.
Food | Contact
Apples | Contact 1
Oranges | Contact 2
Pineapples | Contact 3
Red Grapes OR Blue Grapes OR Purple Grapes OR Green Grapes | Contact 4
Pears | Contact 5
...and so on.
Best Answer
-
Sam,
I'm not sure that this will fix the entire formula, but I do see an issue. Each IF statement needs to stay open if you're nesting another IF within it. Right now you have close parenthesis at the end of each contact name, which tells it to stop thinking.
=IF(Line@row = "Apples", {Contact 1}, IF(Line@row = "Oranges", {Contact 2}, IF(Line@row = "Pineapples", {Contact 3}, IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}, IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}, IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}, IF(Line@row = "Strawberries", {Contact 7}, IF(Line@row = "Pickles", {Contact 8}, IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9})))))))))
Basically, you save all of the end parenthesis associated with the IF statements until the very end. You'll end up with the same number of end parenthesis at the end of the formula as the number of IF statements in your formula.
Let me know if it works!
Best,
Heather
Answers
-
Sam,
I'm not sure that this will fix the entire formula, but I do see an issue. Each IF statement needs to stay open if you're nesting another IF within it. Right now you have close parenthesis at the end of each contact name, which tells it to stop thinking.
=IF(Line@row = "Apples", {Contact 1}, IF(Line@row = "Oranges", {Contact 2}, IF(Line@row = "Pineapples", {Contact 3}, IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}, IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}, IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}, IF(Line@row = "Strawberries", {Contact 7}, IF(Line@row = "Pickles", {Contact 8}, IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9})))))))))
Basically, you save all of the end parenthesis associated with the IF statements until the very end. You'll end up with the same number of end parenthesis at the end of the formula as the number of IF statements in your formula.
Let me know if it works!
Best,
Heather
-
Heather,
Thank you very much! This fixed my problem, and the explanation is extremely helpful for my thought process.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives