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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

  • 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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

  • Hi Jeff,

    Thank for you the reply. I think the formula you are providing would be used on the submission sheet, where as I need the formula to reference the submission sheet such as below:

    So the sheet being referenced is a submission sheet from a form input which can house many different asset ID's based on submission. I don't think I'm doing a great job of explaining it.

  • The max collect portion works separately however, when nested I get the #unparseable error

    I think I might be missing a parenthesis somewhere?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭

    Looks like an extra parentheses at the end. See how the very last end parentheses is black, but the very first open parentheses is blue?

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

  • I've deleted the last parenthesis but still receive the error

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭

    Ah - the function should be COUNTIFS, not COUNTIF. You're giving it multiple criteria so it needs to be COUNTIFS.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

  • I changed to countifs then noticed I was missing a " after the last "No". But now I'm getting #incorrect argument


  • 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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

  • That solved it, thank you sir!