How can I get the most recent value added to a column in relation to a specific value or condition?

Arwin Ayson

I have an inventory monitoring system which contains information on who are the assignee of a specific equipment. For example, a laptop may have an assignee, and the system will have that information. But the laptop may have different assignees over time until it gets disposed. In my setup, I have two sheets, the first sheet (Sheet A) contains data about the equipment, i.e., an asset tag, the model/brand of the equipment, acquisition date. The second sheet (Sheet B) would contain the assignees for the equipment, i.e. the assignee name, assignee's group.

The common columns between the two sheets are the asset tag of the equipment, the assignee name, and the assignee's group. What I did was to use the functions INDEX and MATCH to get the assignee name for a particular equipment basing it on asset tag data. The formula is encoded in Sheet A for the column for the assignee and it gets the assignee value from Sheet B. The formula looks like this:

=INDEX({Sheet - Asset Tracker - Assignee Range 1}, MATCH([Asset Tag]@row, {Sheet - Asset Tracker - Assignee Range 2}, 0))

But I cannot get it to work in such a way that I get the most recent or the current assignee for a particular equipment, especially if the equipment has already been assigned/reassigned over time. Any ideas on how I can get this to work?

Paul Newcome

If you have the form populate at the top, then you won't have to adjust anything in your Main Sheet.

To flip the order so that already submitted forms are going from the top down, insert a system generated Created (date) column, sort on this in descending order, then delete the column.

Paul Newcome

How are you logging when the equipment changes assignee?

Arwin Ayson

Hi Paul,

I created a form where the assignee data are captured and stored in Sheet B. It just add new rows to the bottom of Sheet B.

Paul Newcome

If you have the form populate at the top, then you won't have to adjust anything in your Main Sheet.

To flip the order so that already submitted forms are going from the top down, insert a system generated Created (date) column, sort on this in descending order, then delete the column.

Arwin Ayson

Hi Paul,

That did the trick. It works and gets the most recent value (based on the equipment tag) on the column searched by formula. :)

Paul Newcome

Happy to help. 👍️

Arwin Ayson

Just to revive this due to a change in the process--in the inventory system, the ones encoding the data are no longer using forms but are now directly encoding into the sheet. Doing this makes the new entries go to the bottom of the sheet. I tried changing the formula for the MATCH function but it doesn't capture the new assignees from the bottom, it just returns "NO MATCH". Any ideas how to address this?

Paul Newcome

Try inserting a Created (date) type column and then a checkbox column with the following:

=IF(Created@row = MAX(COLLECT(Created:Created, [Asset Tag]:[Asset Tag], [Asset Tag]@row)), 1)

Then in the sheet that collects the most recent entry, change the INDEX/MATCH to this...

=INDEX(COLLECT({Source Sheet Assignee Name}, {Source Sheet Asset Tag}, @cell = [Asset Tag]@row, {Source Sheet Checkbox Column}, @cell = 1), 1)

BWheel

I am trying the same thing, though all my data is in one sheet. Trying to use MAXIF for this but getting a syntax error.

Can you spot a problem in this formula?

=IF(ISBLANK([Pr Ap Sta]@row), (MAXIFS([Created]:[Created],[Project ID]:[Project ID],[Project ID]@row, [Pr Ap Sta], "<>")), [Pr Ap Sta]@row)

Paul Newcome

@BWheel There is no MAXIF or MAXIFS function in Smartsheet. You would need to use a MAX/COLLECT combo.

BWheel

I created my question as a new thread with more specifics. Smartsheet support was not able to help with a formula either.

Paul Newcome

@BWheel All you need to do is replace the MAXIFS with a MAX/COLLECT.