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

Kurt Robohm

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?

Kurt Robohm

Perfecto! Thank you. Works great!

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

Perfecto! Thank you. Works great!

lclarkNE

I attempted to use this automation to update a column with a check box to Yes but it checked all the rows even though it did not contain the criteria.