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

jan aelbrecht
jan aelbrecht ✭✭✭✭
edited 07/13/23 in Formulas and Functions

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

  • Itai
    Itai ✭✭✭✭✭✭

    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

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • jan aelbrecht
    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
    Itai ✭✭✭✭✭✭

    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

    Project Manager | Transformation Department

    Gong cha

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • jan aelbrecht
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!