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!!
-
this works perfectly and what if I need to add an additional condition. So I am trying to pull the latest entry for the name as well as if one of the columns "Action Type" has "Inventory Order" as the value
-
@CARO885 You would include another range/criteria set within the COLLECT function.
-
thank you that worked! I now have to pull the information from their latest entry on another sheet. I am trying to pull the inventory count data for each person's latest entry. I was trying to use a combination of IF (the column was checked) and Vlookup (looking for the tech's name and returning each column) but I haven't been able to make it work
-
@CARO885 You would use an INDEX/COLLECT.
=INDEX(COLLECT({Range To Pull Over}, {Name}, @cell = "Name Here", {Checkbox}, @cell = 1), 1)
-
@Paul Newcome I wasn't able to make it work, any chance you could help me by telling me what I should type out based on the column names I have?
-
@CARO885 You only need to create cross sheet references. The cross sheet references in my sample formula indicate which column you should be referencing for each.
Help Article Resources
Categories
Check out the Formula Handbook template!