Why is my INDEX/COLLECT formula not working?
![K8dGr8](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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!!
Answers
-
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.
If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
-
Hi @K8dGr8,
I started to test this with a similar setup, but noticed that the formula you provided as being in the Date Stamp column references the Date Stamp column, and is the same as the formula in the Most Recent Update column - it seems like you might have copied the wrong one?
Could you provide the formula that’s in the Date Stamp column in the ‘Activity Log’ sheet? Once I’ve got that, I can test with a similar setup and that should help me and/or other members determine what’s not quite right in your formula in the Database sheet!
Thanks,
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Georgie,
You are right. I accidentally gave you duplicate formulas. Here are the correct formulas for each.
Activity Log Fields
Date Stamp
: =IFERROR(VALUE((YEAR(Created@row) * 10000) + YEARDAY(Created@row)), 0)Most Recent Update
: =IF([Date Stamp]@row = MAX(COLLECT([Date Stamp]:[Date Stamp], [Seat ID]:[Seat ID], [Seat ID]@row)), true, false)Database Field - data I'm trying to populate by indexing information in Activity Log
Contact Name
=INDEX(COLLECT({Activity Log | Contact Name}, {Activity Log | Most Recent Update}, true, {Activity Log | Seat ID}, [Seat ID]@row), 1)Thanks a ton, Georgie!
-
Thanks for providing the formulas! I’ve tested and I got this working on my end, but I also see INVALID VALUE when there’s no matching Seat ID between the two sheets. This is expected, since the formula can’t return a contact name when it can’t find a match on one of the provided criteria in the formula. Take a look here for more information.
Several questions on your setup before we go any further:
- Is the Seat ID column in the Database sheet formatted with a leading 0 so that the values match those in the Seat No. column in the Activity Log sheet?
- Have you confirmed that all rows in the Database sheet have rows in the Activity Log sheet with matching Seat IDs?
- Am I correct in saying that the Seat No. column in the Activity Log is manually entered (either via the form or directly into the sheet)?
- Are you getting the INVALID VALUE error on all rows in the Database sheet, or only certain rows?
If you need to keep the Seat ID column in the database sheet as an auto-number column, you’ll need to populate any other column in all rows up to the latest Seat ID number in your Activity Log sheet, and then save the Database sheet, so that the formula can match values. To clarify, if the highest seat ID number in the Activity Log sheet is, for example, 099, you’ll need to ensure that the Auto-Number column in the Database sheet is set up with a leading 0 and that at least 99 rows of the sheet are populated with data, so that the Seat ID ‘099’ is present in the Database sheet.
Alternatively, if you’re able to change the Seat ID column in the database sheet to a Text/Number column, you could manually enter the seat ID in that sheet to have the formula in the Contact name column pull the corresponding value through.
If you’re still having trouble, could you also provide a screenshot of your Database sheet showing where the error is presented, and a screenshot showing the corresponding rows in the Activity Log?
Thanks,
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!