Formulas in Workspaces

Amanda Wax

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!

Andrée Starå

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!

Bassam Khalil

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 (" ").

Toufong Vang

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

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å

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!

Amanda Wax

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å

@amandaW

Excellent!

You're more than welcome!