Is it possible to change the formula in one cell of a formulated column?

Options

Best Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/04/23 Answer ✓
    Options

    Hi @tjernejcic, the answer is no and yes. If it's a column formula, you can't change the formula in that one cell, but you can create a dependency that only triggers on that one row, as long as there is something unique in that row that you can point to. To make is easy, create a helper column called "OneCell". Then, in that helper column, put an X in the row you want different from every other row. Go ahead and hide that column.

    Now go back to your original formula you want to change. Let's say that column formula used to be:

    =IF([Column A]@row="Complete","Done","Not Done")

    Now you want that one cell in the formula column to have its own formula, which we'll just call <NEW FORMULA>. Using the column OneCell, you can change that formula to read:

    =IF(OneCell@row="x", <NEW FORMULA>, IF([Column A]="Complete","Done","Not Done"))

    The first statement, "IF(OneCell@row="x"," will only be true for the row where you made an "x" in the OneCell column. That is the only cell in the column that will trigger your <NEW FORMULA>. Otherwise, the original formula will activate. You don't need to create the helper column OneCell if there are any other unique IDs on that row that you can reference (maybe you have an auto-numbered column which you can point to).

    Let me know if this helps, and please flag this post if I answered your question! Good luck!

  • tjernejcic
    tjernejcic ✭✭
    Answer ✓
    Options

    Thank you Lucas!

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/04/23 Answer ✓
    Options

    Hi @tjernejcic, the answer is no and yes. If it's a column formula, you can't change the formula in that one cell, but you can create a dependency that only triggers on that one row, as long as there is something unique in that row that you can point to. To make is easy, create a helper column called "OneCell". Then, in that helper column, put an X in the row you want different from every other row. Go ahead and hide that column.

    Now go back to your original formula you want to change. Let's say that column formula used to be:

    =IF([Column A]@row="Complete","Done","Not Done")

    Now you want that one cell in the formula column to have its own formula, which we'll just call <NEW FORMULA>. Using the column OneCell, you can change that formula to read:

    =IF(OneCell@row="x", <NEW FORMULA>, IF([Column A]="Complete","Done","Not Done"))

    The first statement, "IF(OneCell@row="x"," will only be true for the row where you made an "x" in the OneCell column. That is the only cell in the column that will trigger your <NEW FORMULA>. Otherwise, the original formula will activate. You don't need to create the helper column OneCell if there are any other unique IDs on that row that you can reference (maybe you have an auto-numbered column which you can point to).

    Let me know if this helps, and please flag this post if I answered your question! Good luck!

  • tjernejcic
    tjernejcic ✭✭
    Answer ✓
    Options

    Thank you Lucas!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

    No problem, @tjernejcic, hope you got everything working!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!