Pull in most recent form entry only

Christa Brown

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 =)

Paul Newcome

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.

Paul Newcome

Are new forms entered at the top of the sheet or the bottom of the sheet?

Christa Brown

At the bottom currently

Paul Newcome

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.

Christa Brown

It worked - thank you SO MUCH!!

Paul Newcome

Happy to help. 👍️

CARO885

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

Paul Newcome

@CARO885 You would include another range/criteria set within the COLLECT function.

CARO885

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

Paul Newcome

@CARO885 You would use an INDEX/COLLECT.

=INDEX(COLLECT({Range To Pull Over}, {Name}, @cell = "Name Here", {Checkbox}, @cell = 1), 1)

CARO885

@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?

Paul Newcome

@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.