How to ignore past entries from the same user?

Marvin Francis

Here is the issue. I am creating a self assessment form. A user would enter their name, ID#, and assess themselves against a competency. In the image below, you see the user has a gap to Access Tech Documents. They then closed the gap to this competency and updated the form to show no gap. My metric is gap percentage, and since this user no longer has a gap, I need to ignore the past entry. So "If ID# is the same, AND created is in the past, ignore past entry. Something like this. Any ideas if/how to do this in Smartsheet?

Thank you,

Paul McGuinness

Hi @Marvin Francis

If this is based on users only completing 1 competency (not 3 or 4 different competencies) then you could add a helper column to identify which rows to include in your gap percentage calculation. I assume there is a date column in your sheet although not shown in your picture.

The formula in the Helper column is:

=IF(Created@row = MAX(COLLECT(Created:Created, User:User, User@row)), "Count", "Ignore")

If you had different kinds of competencies you would expand this out to include a competency type column, something like:

=IF(Created@row = MAX(COLLECT(Created:Created, User:User, User@row, Competency:Competency, Competency @row)), "Count", "Ignore")

The competency formula just illustrates how that would be done if needed.

Hope this helps

Paul

Paul McGuinness

Hi @Marvin Francis

If this is based on users only completing 1 competency (not 3 or 4 different competencies) then you could add a helper column to identify which rows to include in your gap percentage calculation. I assume there is a date column in your sheet although not shown in your picture.

The formula in the Helper column is:

=IF(Created@row = MAX(COLLECT(Created:Created, User:User, User@row)), "Count", "Ignore")

If you had different kinds of competencies you would expand this out to include a competency type column, something like:

=IF(Created@row = MAX(COLLECT(Created:Created, User:User, User@row, Competency:Competency, Competency @row)), "Count", "Ignore")

The competency formula just illustrates how that would be done if needed.

Hope this helps

Paul

Marvin Francis

seriously thank you, this is a big help

Marvin Francis

@Paul McGuinness looks like the second formula is the one I used, thank you. I had multiple competencies. I created a helper column per competency, then replaced "Competency" in the formula you provided, with my respective competency name. This works perfectly, thank you.

Paul McGuinness

@Marvin Francis

Not a problem at all, glad i could help

Paul