How to set a value in one column based on value of another column

I have 3 columns:

"auto complete fill", "status", and "% complete"

I set % complete manually, but would like to automatically set it to 100% when status = Complete.

Since the formula get's overwritten when I manually set % complete, I created the helper column "auto complete fill" to hold a formula, hoping to use the formula to set the value of % complete if status = complete.

This is the formula I attempted to use in the auto complete cell: =if (status@row = "complete", [% complete]@row =100, "")

I get an error invalid column value. Seems SS won't let me set a new value in a different cell. The formula always assumes you're referencing the current cell. Is there a way to do this?

Best Answer


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Kurt Robohm,

    You can do this a bit more easily with automation and without needing the auto-complete fill column addition:

    As you're entering the %age complete manually then a formula would be overwritten - if you had several status stages with defined %ages attached then you could use a nested IF statement instead to update the percentage instead.

    Hope this helps; if you've any questions then just ask!

  • Kurt Robohm
    Kurt Robohm ✭✭
    Answer ✓

    Perfecto! Thank you. Works great!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!