Why is my INDEX/COLLECT formula not working?

K8dGr8

I have a very large contact database that has columns of information need to be updated periodically. I'd like to use a separate sheet with form as an activity log, where each new row contains the updated information for a specific contact in the database. To do this, each contact in the database has a unique ID. When submitting the activity log form, the user must input the unique contact ID.

Over time, there will be multiple rows with the same contact ID, and my database only wants to reference the activity log row that's most recent. Therefore, I have a helper column in the activity log that identifies which rows have the most recent info for each contact ID. This is a checkbox column. Still following?

The formula I'm using in the database to reference the contact name in the activity log row with the most recent information is returning error #INVALID VALUE. I feel like it has something to do with the database's [Seat ID] column being formatted as an Auto-number, but don't know how to get around this since this is the only column I have that ties the two sheets together.

Any ideas how to fix this?

DATABASE

Contact Name=INDEX(COLLECT({Activity Log | Contact Name}, {Activity Log | Most Recent Update}, true, {Activity Log | Seat ID}, [Seat ID]@row), 1)

ACTIVITY LOG

Date Stamp: =IF([Date Stamp]@row = MAX(COLLECT([Date Stamp]:[Date Stamp], [Seat ID]:[Seat ID], [Seat ID]@row)), true, false)

Most Recent Update: =IF([Date Stamp]@row = MAX(COLLECT([Date Stamp]:[Date Stamp], [Seat ID]:[Seat ID], [Seat ID]@row)), true, false)

Thank you in advance for your help!!

NicoLHC

Dear @K8dGr8 , its always fast if you could share the sheet with dummy data. nico.roepnack@lighthouseconsultings.com

Also if you have already AI in Smartsheet you could use this help or visit our AI Smartsheet Propmt engineering webinar.