Formula help for multiple if statements that pull in a field value
Hi.
I have a form that I am writing into Smartsheet. Part of the form has a drop down for the Analysis Type. Some of those types will cause another field to show to obtain additional information. I need to keep them as separate fields, but I'm trying to create a formula field that will use the Analysis Type and pull in a label and then the value from the applicable field. For example, if they choose Brand Analysis, it would return Brand Analysis: Brand A. I am getting an unparseable error. I'm not sure if it is because of my formula or if it is because I can't pull in a field value into a formula. I put for it to return no value for the false, but I may have that syntax wrong.
=IF([Analysis Type]@row = "Brand Analysis", "Brand Name: [Brand Name]",
IF([Analysis Type]@row = "Hotel Analysis", "Spirit Code for Analysis: [Spirit Code for Analysis]",
IF([Analysis Type]@row = "Partner Analysis", "Partner Name: [Partner Name]",
IF([Analysis Type]@row = "Region Analysis", "Region Name: [Region Name]",
IF([Analysis Type]@row = "Other","Other Analysis Type: [Other Analysis Type]",” “)))))
Thank you for any help you can provide!
Sarah
Answers
-
Hi Sackerman70
It appears that there might be a syntax mistake in your formula. To better understand the issue and assist you, could you please provide some screenshots highlighting the columns you are using? This would greatly help us in diagnosing and resolving the problem.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Hi Kaveri,
Thank you for your response. I appreciate your help. This is an example of the columns I'm using in my formula. They are all text/number fields.
Sarah
-
There are a number of syntax issues to include an issue with some quotes (more below).
Give this a try:
=IF([Analysis Type]@row = "Brand Analysis", "Brand Name: " + [Brand Name]@row, IF([Analysis Type]@row = "Hotel Analysis", "Spirit Code for Analysis: " + [Spirit Code for Analysis]@row, IF([Analysis Type]@row = "Partner Analysis", "Partner Name: " + [Partner Name]@row, IF([Analysis Type]@row = "Region Analysis", "Region Name: " + [Region Name]@row, IF([Analysis Type]@row = "Other","Other Analysis Type: " + [Other Analysis Type]@row)))))
Quotes issue:
I removed the final set of quotes from the formula as they are not needed, but always make sure you are typing formulas directly in Smartsheet or in a text editor such as Notepad (not Word). That last set of quotes in your formula were slanted as opposed to straight up and down like the rest of them. These slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in Smartsheet formulas.
-
Thanks Paul! That worked. I appreciate your help. It is amazing how one little change can make or break the formula!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!