Formula help for multiple if statements that pull in a field value

sackerman70

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

Kaveri Vipat

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.

sackerman70

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

Paul Newcome

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.

sackerman70

Thanks Paul! That worked. I appreciate your help. It is amazing how one little change can make or break the formula!

Paul Newcome

Happy to help. 👍️