Pull in most recent form entry only
Hello!
I am trying to add an indicator to use for a report to only recognize the most current form entry for a date range. Specifically, I am using a auto fill "created" column and the collect functionality as follows:
=IF(ISBLANK(Created@row), "", MAX(COLLECT(Created:Created, [Week Indicator]:[Week Indicator], "1")))
However, this isn't really the greatest way to do it since I have to do multiple checks.
Is it possible to create a formula where I can check for the Team member name, matching start date, and apply an indicator if the Created Date is the max value?
Below is a snip of the form I'm making. The column "Most Current View" is where the IF formula above currently sits, but it's not working right =)
Best Answer
-
Ok. To check a box for the most recent entry for each person, here is what I suggest...
Add an auto-number column. Formatting doesn't matter.
Add a text/number column called "Row #" and enter the following as a column formula:
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Add a checkbox column with the following as a column formula:
=IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Team Member Name]:[Team Member name], [Team Member Name]@row)), 1)
This will check the box for the most recent entry for each team member.
Answers
-
Are new forms entered at the top of the sheet or the bottom of the sheet?
-
At the bottom currently
-
Ok. To check a box for the most recent entry for each person, here is what I suggest...
Add an auto-number column. Formatting doesn't matter.
Add a text/number column called "Row #" and enter the following as a column formula:
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Add a checkbox column with the following as a column formula:
=IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Team Member Name]:[Team Member name], [Team Member Name]@row)), 1)
This will check the box for the most recent entry for each team member.
-
It worked - thank you SO MUCH!!
-
Help Article Resources
Categories
Check out the Formula Handbook template!