Find most recent updated status for category
Hi all,
I'm searching for a solution on following idea:
I have a sheet with following four columns:
Created (date) | Category (Dropdown) | Previous Status (RYG balls) | Current Status (RYG balls)
Now I want to auto-populate "Previous Status" with the last "Current Status" of the same "Category" when a new row is entered. One example:
2020-02-15 | A | Green | Yellow
2020-02-15 | B | Green | Green
2020-02-18 | A | Yellow | Red
When editing a new row and selecting Category = A I want to see "Red" in "Previous Status".
Is that possible somehow?
Answers
-
Are new rows added at the top or the bottom of the sheet?
-
At the bottom would be most conveinient.
If it's necessary for the solution to have this on top I would be fine with it as well.
-
It is actually an easier solution with them being populated at the bottom. Put this in Row 1 and dragfill down.
=INDEX(COLLECT([Current Status]:[Current Status], Created:Created, @cell <= Created@row, Category:Category, @cell = Category@row), 1)
-
Hi Paul,
thank you for the formula.
It works for some entries. For others I'm guessing the time stamp is simply the blocker here?
I also find the behaviour that Prev. Stat. would populate the Curr. Stat. in case no Curr. Stat. was given.
-
Yes. The issue is the time stamp. You have multiple rows for the same category with the same exact time stamp.
-
Ok, I understand. I will work something out with this solution.
Thank you for your support, Paul!
-
Happy to help. 👍️
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!