Find most recent updated status for category

Heiko E.

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?

Paul Newcome

Are new rows added at the top or the bottom of the sheet?

Heiko E.

https://community.smartsheet.com/discussion/comment/257768#Comment_257768

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.

Paul Newcome

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)

Heiko E.

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.

Paul Newcome

Yes. The issue is the time stamp. You have multiple rows for the same category with the same exact time stamp.

Heiko E.

Ok, I understand. I will work something out with this solution.

Thank you for your support, Paul!

Paul Newcome

Happy to help. 👍️