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

Perfecto! Thank you. Works great!
Answers

Hi @Kurt Robohm,
You can do this a bit more easily with automation and without needing the autocomplete 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!

Perfecto! Thank you. Works great!

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.
Help Article Resources
Categories
Check out the Formula Handbook template!