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 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!
-
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!