Pull latest formula help - index/collect/max

Allie12380

Trying to use Max(Collect) but keep getting #INVALID REFERENCE#

=MAX(COLLECT({Sheet A Date}, {Sheet A- Date column}, {Sheet A- Equipment Name column}, [Equipment Name]@row))

But having trouble applying these to my scenario. How do I update these for my scenario?

Sheet "Device Master" has Device Barcode field (and lots of other fields)

Sheet "Device Use" has Device ID (same as Device Barcode), User ID, Device Status (Check in, check out), Created Date, Modified Date

On "Device Master" I want to include column that pulls the latest User ID for the Device Barcode. Goal is to see the User ID of the last person who had the device.

Maybe I am messing up what goes where.

MAX(COLLECT: Reference range from source sheet Device Use, User ID column that I want to pull in from Device Use sheet, Device ID on Device Use, destination sheet Device Master column Device Barcode@row column

=MAX(COLLECT({DeviceUserUserID}, {User ID}, {Device ID}, [Device Barcode]@row))

Any help or suggestions would be greatly appreciated. Maybe also need an Index somewhere in this or look at the Created Date.

I think this is more like what I need but cant get it to work.
=INDEX(COLLECT({DeviceUserUserID}, {DeviceUseDeviceID}, [Device Barcode]@row, {DeviceUseCreateDate}, MAX(COLLECT({DeviceUseCreateDate}, {DeviceUserDeviceID}, [Device Barcode]@row))), 1)

Here is what get with index.collect.max:

jmyzk_cloudsmart_jp

Hi @Allie12380

If you need to determine the latest User ID of the user who last checked out a device, there are two approaches based on whether the row order remains unchanged or not.

https://app.smartsheet.com/b/publish?EQBCT=8c2c1ceece2f4d39a612502b03d0770e

Approach 1: If the Row Order Will Not Change

If the data remains in the same order as entered, you can simply collect all "Check Out" entries for a device and retrieve the last one using COUNT(COLLECT(...)) to determine the latest entry.

[Latest User ID]

=IF(COUNT(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out")) > 0, INDEX(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out"), COUNT(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out"))))

This formula:

Collects all User ID values where the device was Checked Out.
Retrieves the last user in the collected list, assuming the data remains in order.

https://app.smartsheet.com/b/publish?EQBCT=6c7b2030c1594a608b616d9583f3cade

Approach 2: If the Row Order May Change

If the order of rows is not fixed as in the device_use 2 sheet below, we need a helper column to identify the most recent check-out and then retrieve the corresponding User ID.

Step 1: Add a Helper Column to Identify the Latest "Check Out"

Create a helper column (Last Check Out?) to check whether the row is the latest check-out entry for a device.

Formula (Helper Column - "Last Check Out?")

[Last Check Out]

=IF([Device Status]@row = "Check Out", MAX(COLLECT([Created Date]:[Created Date], [Device ID]:[Device ID], [Device ID]@row, [Device Status]:[Device Status], "Check Out")) = [Created Date]@row)

This formula:

• Checks if the row's "Check Out" entry is the latest one for that device.
• Returns true for the row with the most recent Check Out date

Step 2: Retrieve the User ID for the Latest "Check Out"

Now, pull the User ID from the row where "Last Check Out?" is true.

Formula:

[Latest User ID 2]

=JOIN(COLLECT({device_use 2 : User ID}, {device_use 2 : Device ID}, [Device Barcode]@row, {device_use 2 : Last Check Out}, true))

This formula:

Filters the Device Use sheet to find the row where "Last Check Out?" is true.
Returns the User ID of the person who last checked out the device.

https://app.smartsheet.com/b/publish?EQBCT=025c830bf8e442789f7d6c12a79c77aa

jmyzk_cloudsmart_jp

Glad it worked out!😁

jmyzk_cloudsmart_jp

Hi @Allie12380

If you need to determine the latest User ID of the user who last checked out a device, there are two approaches based on whether the row order remains unchanged or not.

https://app.smartsheet.com/b/publish?EQBCT=8c2c1ceece2f4d39a612502b03d0770e

Approach 1: If the Row Order Will Not Change

If the data remains in the same order as entered, you can simply collect all "Check Out" entries for a device and retrieve the last one using COUNT(COLLECT(...)) to determine the latest entry.

[Latest User ID]

=IF(COUNT(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out")) > 0, INDEX(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out"), COUNT(COLLECT({User ID}, {Device ID}, [Device Barcode]@row, {Status}, "Check Out"))))

This formula:

Collects all User ID values where the device was Checked Out.
Retrieves the last user in the collected list, assuming the data remains in order.

https://app.smartsheet.com/b/publish?EQBCT=6c7b2030c1594a608b616d9583f3cade

Approach 2: If the Row Order May Change

If the order of rows is not fixed as in the device_use 2 sheet below, we need a helper column to identify the most recent check-out and then retrieve the corresponding User ID.

Step 1: Add a Helper Column to Identify the Latest "Check Out"

Create a helper column (Last Check Out?) to check whether the row is the latest check-out entry for a device.

Formula (Helper Column - "Last Check Out?")

[Last Check Out]

=IF([Device Status]@row = "Check Out", MAX(COLLECT([Created Date]:[Created Date], [Device ID]:[Device ID], [Device ID]@row, [Device Status]:[Device Status], "Check Out")) = [Created Date]@row)

This formula:

• Checks if the row's "Check Out" entry is the latest one for that device.
• Returns true for the row with the most recent Check Out date

Step 2: Retrieve the User ID for the Latest "Check Out"

Now, pull the User ID from the row where "Last Check Out?" is true.

Formula:

[Latest User ID 2]

=JOIN(COLLECT({device_use 2 : User ID}, {device_use 2 : Device ID}, [Device Barcode]@row, {device_use 2 : Last Check Out}, true))

This formula:

Filters the Device Use sheet to find the row where "Last Check Out?" is true.
Returns the User ID of the person who last checked out the device.

https://app.smartsheet.com/b/publish?EQBCT=025c830bf8e442789f7d6c12a79c77aa

Allie12380

Thanks! Going to try this today and will follow up later.

Allie12380

@jmyzk_cloudsmart_jp thank you!!!!! It worked. I went with option 2 because I can't say no one would ever change the row order. So felt that was the better sustainable option.

jmyzk_cloudsmart_jp

Glad it worked out!😁