copy cell with formula that start with 'sign to other cell without 'sign
Hi,
we populate a cell with a formula that we create in a different sheet.
we copy the line to this work sheet and to be able to do this we add in
front of the formula the sign '
looks like this : '=IF(COUNTIF(COLLECT([Task Completed by user]:[Task Completed by user], [Task Nr]:[Task Nr], ="TSK021",[Entity]:[Entity], =[Entity]@row), 1) = 1, "", "NOK_TSK021")
If I now copy this formula to another cell in this sheet with ctrl C / ctrl V the formula is still the same
this means no calculation is done
problem is of course that the formula start with '=IF...
question = how can I remove this ' sign in front of the formula line.
a find and replace is not working in Smartsheet
regards
jan
Answers

Hey @jan aelbrecht,
Why are you adding ' to the formula before copying it? just to keep it as text instead of a formula?
If you double click on the cell can you not delete the ' and use the formula?
Is the formula a column formula?
Let me know and maybe share a screenshot that could help.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itaiperez/

Hi,
We create the formal in a different sheet based on some options, this formula we need to use in our worksheet.
in this worksheet we work with a vlookup to import this formula, but to be able to do this we needs to make it as text. to use the formula we need to remove the ' in front of the for.
this can be done one by one, correct but we have about 1500 lines
it's a column formula yes.

Hey @jan aelbrecht,
Can you add some screenshots please?
If you remove the ' from one formula and then drag it down to all 1500 lines would that not work?
I think some screenshots will really help to understand the situation better.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itaiperez/

Hi,
The left screen you see when you hover over the cell, the right screen is when you double click on the cell.
problem is that a copy / paste off this cell into another cell does not remove the ' sign
also a find an replace in Smartsheet does not find this ' in the cell
when I copy the cell to excel and try to find and replace in excel = also nothing found.
doing this in notepad++ is an option and copy these back to Smartsheet.
but a lot of work to do very month.
regards
jan
Help Article Resources
Categories
Check out the Formula Handbook template!