Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Why is my INDEX/COLLECT formula not working?

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!!

Tags:

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=ebdsshcopyurl

    We 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.

    🎥 YouTube 🚀TimeLine View

    http://lighthouseconsultings.de/

  • Employee

    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!

  • Employee

    Hi @K8dGr8 @kpearson,

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions