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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!