How to ignore past entries from the same user?

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,



Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    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

  • seriously thank you, this is a big help

  • @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
    Paul McGuinness ✭✭✭✭✭✭

    @Marvin Francis

    Not a problem at all, glad i could help

    Paul

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!