How can I record "old answers?

Hector Mendez

Hello,

I was wondering if there was a way to record old/initial updates to a cell.

I am trying to record the most recent Z# to the most recent cage cart update. The problem exists when the same cage cart is spotted with a defect.

The sheet on the left contains the carts that we have found to have a defect with the appropriate Z#. While the sheet on the right is a time-stamped user input sheet. The second sheet will contain the Z# tied to the cage cart indicated.

It would be great if I could stop updating the cells on the left after 4 hours (standard processing time). So if the cart is encountered with a defect (left sheet) it will only update with a Z # if the right sheet has a time stamp between today and 4 hours.

The probability of the same cart re-appearing is low, but after enough time it will happen and any previous data will be lost.

=IFERROR(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), "No match found")

Genevieve P.

Hi @Hector Mendez

Smartsheet doesn't currently have a time function so identifying the 4 hour window would be difficult. That said, you can add another range and criteria to your COLLECT function to ensure that the Created date is Today's date, if that would help?

=IFERROR(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 {LWW Cage Cart Tracker Date Column}, @cell = TODAY()), 1), "No match found")

If I've misunderstood and you're looking to keep the first value even when a second one is entered that day, then what I would suggest doing is use a JOIN(COLLECT formula instead of an INDEX(COLLECT and then bring in more than one Z#.

Ex:

=IFERROR(JOIN(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), " / "), "No match found")

However then you'd also need to adjust what the formula is in your Checkbox column, as this is what's filtering down your values in the sheet on the right and excluding the first C0001 value.

Is there anything else on the original sheet that we can use to narrow down the Z# needed? For example, is there a Status column which would indicate that the Z# should be brought in to your left sheet still?

Cheers,

Genevieve