change status based on previous row
Is there a way to have a formula change the status column when the row above it changes. For example when the status in Row 1, changes to "Complete" the status in row 2 will change to "Ready to Start" I know I can't put a formula in the drop down so I imagine some sort of helper column would be needed. or could this be done through automations instead?
Answers
-
I did work out to create a helper column that the box will check in the 2nd row if Status1(in row 1) changes to "complete" using the formula:
=IF(Status1 = "Complete", 1, 0)
and then set up an automation for when the box gets checked to update the Status cell to "ready to start" however this formula is not able to be copied down the column so it does require that I manually enter it for each row to be dependent on its correct predecessor (just the row above) So I am still interested in if there is an easier/different way to do this. -
To get a column formula to work, you first need an auto-number column (called "Auto" in this example) and then a text/number column (called "Row" in this example) containing the following column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then the formula in the checkbox column would be:
=IF(IFERROR(INDEX(Status:Status, Row@row - 1), ".") = "Complete", 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 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!