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

  • Kaveri Vipat
    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.

    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

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!