At Risk Sheet Summary

Lindsay Scarey

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!

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