How to pull only most recent value?

R. Smythe

Hi! I am trying to build a time allocation table for resources to track their time on projects by %age of time spent that week. Currently I use a weekly update request, with one row per TM that gets updated weekly. I would like to change to a form, where a new row is created each time the TM logs time, with the time/date stamp, so that we could eventually track time devoted to project over time. However I have a lot of reports that should only show the most recent time allocation entry. So in english what I would want is:

"Do a VLOOKUP on the employeeID, then return the value from column 3 where the volume in column 4 is the highest"

I can think of how to do it in SQL, and could figure it out in a traditional spreadsheet app, but not sure how to do it here?

Paul Newcome

I would set it so that new forms populate at the top of the sheet.

You could then enter a (hidden) auto-number column with no special formatting needed called "Auto", a (hidden) text/number column called "Row" with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

and then a (hidden) checkbox column with this column formula:

=IF(COUNTIFS(employeeID:employeeID, @cell = employeeID@row, Row:Row, @cell<= Row@row) = 1, 1)

This will check the box for the most recent entry for each person and will allow you to easily create a report filtered by this box being checked.

Paul Newcome

I would set it so that new forms populate at the top of the sheet.

You could then enter a (hidden) auto-number column with no special formatting needed called "Auto", a (hidden) text/number column called "Row" with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

and then a (hidden) checkbox column with this column formula:

=IF(COUNTIFS(employeeID:employeeID, @cell = employeeID@row, Row:Row, @cell<= Row@row) = 1, 1)

This will check the box for the most recent entry for each person and will allow you to easily create a report filtered by this box being checked.

kward

I reworked it to the following

=MAX(COLLECT({Building Time Tracking Range 1}, {Building Time Tracking Range 3}, "Employee Name"))

And am now getting an Invalid Command Value error.

The field on the Building Time Tracking sheet is a Contact List field, would that cause issues with the command?