How to pull only most recent value?

Options

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?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    kward ✭✭
    Options

    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?