At Risk Sheet Summary

I am hoping to create a sheet summary formula that will flag a something as "at risk" if it is past five days since the date in row 1 of the "Oldest App on Hand" column. I need it to only pull from that one cell, and be indexed to row one as more rows are added over time.

Thank you in advance for any help!


Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Lindsay Scarey

    First, to determine Row1, I would add two helper columns, [Row ID] (Auto-Number) and [Row No]. If you add a new row at the top, as you would with a form, you can not use [Row ID] to determine the row position, so we use the MATCH function to get the [Row No].

    [Row No] = MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    Then, you can get [Oldest App On Hand]@row1 with this formula;

    INDEX(COLLECT([Oldest App on Hand Entered]:[Oldest App on Hand Entered], [Row No]:[Row No], 1), 1)

    The condition, "if it is past five days since the date in row 1 of the "Oldest App on Hand" column" is as follows;

    TODAY() - [Oldest App On Hand]@row1 >5

    TODAY() >[Oldest App On Hand]@row1 + 5

    So, the formula to populate the At Risk Flag in the summary field is as follows;

    [At Risk] =IF(TODAY() > INDEX(COLLECT([Oldest App on Hand Entered]:[Oldest App on Hand Entered], [Row No]:[Row No], 1), 1) + 5, 1, 0)

    I created the At Risk field as a text/number, inputted the formula, and then converted it to a symbol, as entering a formula into a symbol field was difficult.

    https://app.smartsheet.com/b/publish?EQBCT=1f5672f7440a46dfa4c4d601b55f8fd5


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!