Using If/Then Formula

MgtCon

I'm new to Smartsheet and have a drop down menu that needs to tie to another column/field I am trying to create a if/then formula but obviously I'm doing something wrong as I keep getting an #UNPARSEABLE error.  I'm trying to say "if Industry Examinations equals Series 24, then add the description, and if not, then leave blank. Help?! Thank you!

=if([Industry Examinations]=Series 24,"150 multiple choice questions; 3 hours and 30 minutes testing time. This examination qualifies individuals required to register as general securities principals in order to manage or supervise the member's investment banking or securities business for corporate securities, direct participation programs, and investment company products/variable contracts.","")

 

Andrée Starå

Hi,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

PStowers

Hi Mgt/Con-

What does Series 24 represent?  Is this part of your drop down text?  If so then it needs to have quotes around it.  Otherwise everything looks fine.  I am assuming that [Industry Examinations] will have the row number after it in the actual case  (example [Industry Examinations]17="Series 24"......   )

 

=if([Industry Examinations]="Series 24","150 multiple choice questions; 3 hours and 30 minutes testing time. This examination qualifies individuals required to register as general securities principals in order to manage or supervise the member's investment banking or securities business for corporate securities, direct participation programs, and investment company products/variable contracts.","")

Hope this helps.  :-)

Andrée Starå

Hi,

Try something like this.

Added @row so you don't have to think about the row numbers. I also removed the last "") False part because it's not needed.

=IF([Industry Examinations]@row = "Series 24"; "150 multiple choice questions; 3 hours and 30 minutes testing time. This examination qualifies individuals required to register as general securities principals in order to manage or supervise the member's investment banking or securities business for corporate securities, direct participation programs, and investment company products/variable contracts.")

The same version but with the below changes for your and others convenience.

=IF([Industry Examinations]@row = "Series 24", "150 multiple choice questions, 3 hours and 30 minutes testing time. This examination qualifies individuals required to register as general securities principals in order to manage or supervise the member's investment banking or securities business for corporate securities. direct participation programs. and investment company products/variable contracts.")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

 

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

MgtCon

PStowers--

Thank you! I had quotes around Series 24 initially but it still didn't work so I deleted them (which obviously also didn't work-lol). Looking at your reply, I put the quotation marks back in and added the row number and voila--it worked great!  Thank you so much--I really appreciate your help!!!

MgtCon

MgtCon

Excellent, Andre, thank you! I added the @row and removed the end quotes and that worked too...now can you explain how I get my next description in line? When I added the below it gave me an #Invalid Column Value..again I'm obviously doing something wrong! I have 6 different Series in my drop down menu and each one has a different description. I thought I knew what I was doing! LOL

=if([Industry Examinations]@row="Series 7","250 multiple choice questions - administered in two parts of 125 questions each; 3 hours testing time for each part. This registration qualifies a candidate to be a Registered Representative and for the solicitation, purchase, and/or sale of all securities products, including corporate securities, municipal securities, municipal fund securities, options, direct participation programs, investment company products, and variable contracts.")

Andrée Starå

Happy to help!

What column type is it?

MgtCon

The column type is text...

Andrée Starå

Strange!

I'd be happy to take a look!

Can you share the sheet with me or a copy of it? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

MgtCon

Hi Andree, I haven't had a chance to get back to this until now. I've been asked not to share the SmartSheet in the community (company policy) but I thought I had figured out the correct formula. Unfortunately, it's saying "unparseable." I'm not sure if I'm nesting the "IF" statement correctly, but I also tried using "IF((OR)" and I feel I'm almost there but need a little tweak in the formula.  I have the Industry Designations in one column, and I'm trying to get the next column to populate based on the drop down choice of the previous column. I also set up the Designation descriptions in another Smartsheet. Now I'm trying to build on the formula you gave me initially. Would you be kind enough to evaluate the formula below? I have 22 designation descriptions and am getting frustrated because this isn't working!  Thank you so much!! 

=IF([Industry Designations]@row = "Chartered Financial Analyst (CFA®)", {Guide to Industry Designations Range 1}, =IF([Industry Designations]@row = "Certified Financial Planner (CFP®)", {Guide to Industry Designations Range 2},=IF([Industry Designations]@row="Chartered Life Underwriter (CLU®)",{Guide to Industry Designations Range 3},=IF([Industry Designations]="Chartered Wealth Manager (CWM)",{Guide to Industry Designations Range 4},