Formulas - is there a max character/option cap, or did I mess something up?

Another formula-related question. Is there a maximum number of options (not sure of the correct term) I can use with the IF function? I'm getting an #UNPARSEABLE error. Here's the formula I'm trying to use:

=IF([Bundled news section heading]126 = "Individual insurance", "Assurance individuelle", IF([Bundled news section heading]126 = "Corporate", "L'entreprise", IF([Bundled news section heading]126 = "Advocis", "Advocis", IF([Bundled news section heading]126 = " Advanced sales articles", "Nouveaux articles sur les techniques avancées", IF([Bundled news section heading]126 = "Advanced sales article: Tax & Planning", "Techniques avancées: Point fiscal", IF([Bundled news section heading]126 = "Bring your own PC (BYOPC)", "Apportez votre ordinateur personnel (AVOP)", IF([Bundled news section heading]126 = "Competitive information", "Renseignements sur la concurrence", IF([Bundled news section heading]126 = "Chinese", "Chinois", IF([Bundled news section heading]126 = "Compliance", "Conformité", IF([Bundled news section heading]126 = "Corporate", "L'entreprise", IF([Bundled news section heading]126 = "CRM2", "MRCC2", IF([Bundled news section heading]126 = "Group benefits", "Garanties collectives”, IF([Bundled news section heading]126 = "Group retirement services", "Services de retraite collectives", IF([Bundled news section heading]126 = "HelloLife", "BonjourLaVie", IF([Bundled news section heading]126 = "Individual distribution", "Distribution individuelle", IF([Bundled news section heading]126 = "Individual insurance", "Assurance individuelle", IF([Bundled news section heading]126 = "Individual insurance operations update", "Nouvelles de l’exploitation, assurance individuelle", IF([Bundled news section heading]126 = "Insurance sales strategies and tools", "Des stratégies et des outils de vente d'assurance", IF([Bundled news section heading]126 = "Mortgages", "Hypothèque", IF([Bundled news section heading]126 = "New Business Now updates", "Le point sur Affaires nouvelles en direct", IF([Bundled news section heading]126 = "Selectpac", "Garanties Sélectpac", IF([Bundled news section heading]126 = "Solutions Banking", "Solutions Bancaires", IF([Bundled news section heading]126 = "Wealth management", "Gestion du patrimoine", IF([Bundled news section heading]126 = "Wealth management operations update", "Nouvelles de l’exploitation, gestion du patrimoine", IF([Bundled news section heading]126 = "Summit", "Sommet")))))))))))))))))))))))))

I realize this is crazy-long, so wondering if that's the issue. I want a secondary column to populate the French version of the first column - a set of 25 dropdown options (Bundled news section heading) in English. I'd like them to be seperate so that I can further use this secondary column with the French version to add in to another set of data in a third cell.

Thanks for helping with my crazy questions!!!

Find more posts tagged with

Formulas

Comments

J. Craig Williams

Jaye,

According to a comment by "Emily" in the Smartsheet blog, there is no limit to the number of nested-ifs (the term for what you are trying to accomplish).

I'm looking into other potential problems and will report back if I find anything.

Craig

J. Craig Williams

Found it.

After Garanties collectives, you somehow have the "wrong" double quotes.

Short answer: change it to the same as the others and it will work.

Long answer:

Believe it or not this: ” is not the same as "

If you zoom in close, you'll see the first is a curved, the second is not.

In hex, the characters are 94 and 22.

Apparently, Smartsheet does not like 94.

Craig

JLC

Hi Craig, thank you SO much for the amazing deduction! I certainly would never have noticed that. You've been a great help!

bmezger

I've also got a very long nested IF statement that I believe is running into the 4k character max limit. I've cut it down to under 4k characters by renaming column headers, using abbreviations when possible, and trimming spaces in the code. When I attempt to apply the nested IF statement, it works for a minute or two, then I get an error when trying to save the sheet.

An error has occured in the application. Please log out and log back into the application before continuing. If the problem persists, please contact support@smartsheet.com for assistance.

formName: ajax

formAction: fa_gridSave

serverStatus: false

serverStatusText: CriticalException

errorCode: 6

Attached is the nested IF statement.

J. Craig Williams

You should contact support@smartsheet.com

Craig