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
-
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
-
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.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!