Return most recent matched result

Hector Mendez

Hello

I am trying to output the most recent "pick TM z#" result based on the most recent cage number match.

In the first image, a user will manually input all carts encountered. The second image shows all carts that have been found to be "defective". The second image holds the information we would like to present to our team to show which cage cart has a TM attached to it.

The issue arises from multiple matches in the data set. As you can see cart 0001 has two results, "ABc" and "updated result". The Second image displays the output chart to indicate the match, but as you can see the output table only shows ABc as a result.

I can see that the index match returns the first match, but I would like to update it according to the new inputs.

User input table:

Output table:

Information will be input through a form. The pick TM z# is where the equation is nested. The form does not require users to input the Z#. (would not have this problem if it did :) )

I was exploring the Max(collect equation but I could not get it to work. Any help would be appreciated.

Paul H

I am unsure why but maybe its the leading zeros or Smartsheet is treating them as numbers.

But as soon as I a "C" to the cart name the formula works

And yes your correct, by adding this you can use IFS formulas looking for both the cart number and checkmark/1.

Devin Lee

You can change the form to input the new data at the top of the sheet so the INDEX/MATCH will find the newest results first.

Hector Mendez

Hey Devin,

Unfortunately, the first sheet pulls the Z# from the second sheet. So even if I reverse the way the form outputs information the Index/match formula will still only output the first match.

I believe that if I am able to figure out the max(collect portion of the formula I would be able to get the most recent TM Z #.

So in this example I would like the output "updated" instead of Z008.

Paul H

I would simplify the column name to just "Cart Number" to make it easier

In the user input table add a checkbox column "Newest".

Make this the column formula for that check box column

=IF(created@row = MAX(COLLECT([Created]:[Created], [Cart Number]:[Cart Number], [Cart Number]@row)), 1)

This should check the most recent submission for each cart,

Hector Mendez

Hey Paul,

Thanks for the tip.

I may have some syntax issues that I cannot work out.

After I work this out. I am assuming I can reference the checkmark or "1" and IF true this would be my return value?

Paul H

I am unsure why but maybe its the leading zeros or Smartsheet is treating them as numbers.

But as soon as I a "C" to the cart name the formula works

And yes your correct, by adding this you can use IFS formulas looking for both the cart number and checkmark/1.

Hector Mendez

Huge shout out to you Paul!

I was able to use the below formula to output my updated Z#.

=INDEX(COLLECT({LWW Cage Cart Tracker Range 2}, {LWW Cage Cart

Tracker Range 4}, [Cart Number (Enter 4-Digit Number)]@row, {LWW Cage Cart Tracker Range 5}, 1), 1)

I have one final question.

Is there a formula that will lock an old cell. As you can see from the picture below, "ZORIGIN" has been erased. I would still like to keep a "history" of the previous user logins. Any recommendations on this one?

Paul H

You can use an automated workflow to lock the row, but not individual cells