Out of Service Formula

Hi Community,

I'm attempting to create a formula showing the OOS status of the most recent submission for each asset based on the Asset ID in form submissions. For example, below Asset ID FD-001201 would be considered out of service, however, I'm not sure how to account for it being the most recent submission if there had been a previous entry where the OOS Tag had been removed.

My assumption is the the formula would look something like IF(Countif(Asset ID = FD-001201), "No", "Yes", if(and(countif(asset ID = FD-00120), OOS Tag Removal Date "Blank", "Yes", "No")) but this wouldn't account for only the most recent submissions.

If someone knows how to account for ONLY most recent submission for a given Asset ID or another way to write the formula, it would be greatly appreciated.


Thanks!

Tags:

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Bryan Letourneau

    You'll want to use a MAX/COLLECT to find the latest "Date OOS tag Applied" date for that Asset ID, and then use that "Date OOS tag Applied" date and the AssetID to determine if the "OOS Tag Removal Date" cell has a value in it (you can use ISBLANK or ISDATE for this.)

    Put this is a date-type column and make sure it works. (Replace the local ranges with remote ranges if looking these up from another sheet.)

    =MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row))

    Once that works, you can use that inside an IF:

    =IF(COUNTIFS([Date OOS tag Applied]:[Date OOS tag Applied], MAX(COLLECT([Date OOS tag Applied]:[Date OOS tag Applied], [Asset ID]:[Asset ID], [Asset ID]@row)), [Asset ID]:[Asset ID], [Asset ID]@row, [OOS Tag Removal Date]:[OOS Tag Removal Date], ISBLANK(@cell)) > 0, "Yes", "No")

    In English: If the count of rows where: the "Date OOS tag Applied" is the latest date for the Asset ID on this row, the Asset ID is the same as the Asset ID on this row, and the "OOS Tag Removal Date" is blank - is greater than 0, then "Yes" it is OOS; otherwise, "No" it is not OOS.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Bryan Letourneau
    Answer ✓

    Nevermind, I needed a parenthesis after the great than 0 part. Thank you so much!


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    In your last criteria, it should be ISBLANK(@cell). Don't put the {OOS Service Log Range 3} range there, just need ISBLANK(@cell).

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!