Formulas in Workspaces

Options

A few questions:

  1. I recently transferred over some sheets to a workspace area and when I did, it appears that it cleared out any formulas or automation from those sheets. Is that how it should work? Did I not transfer it correctly?
  2. Since all my formulas are now gone, i've manually tried a few times to enter the formula that was in the copied sheet, and it won't work for me. I get an UNPARSEABLE or the formula just remains in the cell without completing the logic and providing a calculation.

Here's the formula I need help on - NOTE this is working fine in another sheet so i know it's not the formula itself:

'=IF([Launch Status]@row = "Not Started", 0, IF([Launch Status]@row = "Open", 0.25, IF([Launch Status]@row = "In Progress", 0.5, IF([Launch Status]@row = "In Review", 0.75, IF([Launch Status]@row = "Cancelled", 1, IF([Launch Status]@row = "Complete", 1, " "))))))

thanks in advance for the help!

Best Answer

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @amandaW

    Hope you are fine, for your questions:

    1- (I recently transferred over some sheets to a workspace area) if you use save as new for your sheet or you move it then the formula will remain working in the same sheet.

    2- ( I get an UNPARSEABLE or the formula just remains in the cell without completing the logic and providing a calculation )

    #UNPARSEABLE

    Cause

    The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

    Resolution

    Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    Ensure that you do not include the apostrophe/single-quote ' when you copy-paste your formula.

    Other than that, there's nothing wrong with your formula in the screengrab above.

    Smartsheet, on the other hand, can be finicky about the sequence of things you do. Ensure that you have a column named Launch Status in the new sheet before you paste the formula. If you're still getting #UNPARSEABLE, then right-click and Clear Contents of those cells/column, and save the sheet. before you paste the formula.

    If you get #UNPARSEABLE, yet again, copy-paste the formula into Notepad and ensure that the formula doesn't use special characters like curly/slanted quotation marks, apostrophes, etc. Replace any curly marks you find with "straight" marks.


  • Amanda Wax
    Options

    thank you.

    Regarding my 2nd question. it is now just not completing the logic and looks like this in smartsheet. so it's getting stuck somewhere. As i mentioned, the same formula works just fine in other sheets, i'm not sure why this is not working

    I have not put any apostrophe's into the formula per the above comment, but Smartsheet adds them. If i remove the apostrophe, i get the same result as shown here in the image.

    any other ideas?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @amandaW

    I hope you're well and safe!

    I think the issue is the column you're adding the formula to.

    What happens if you add to a different column?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Amanda Wax
    Options

    Thank you! I still don't know WHY it was doing that but i did create several new columns. eventually one column worked. No idea what happened....thanks so much for your help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @amandaW

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!