copy cell with formula that start with 'sign to other cell without 'sign

jan aelbrecht

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

Itai Perez

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.

jan aelbrecht

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.

Itai Perez

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.

jan aelbrecht

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