Index Collect Looking at Auto Number Column

I have an index collect statement in one sheet that uses a column with an auto number value in another table. I get an invalid value error with the formula. However, if I change the column type to text/number from auto number it works.

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Ron Anderson

    I was able to replicate what you're seeing! If your referenced column is being recognized as text, you can adjust your formula to find the value like so:


    =INDEX(COLLECT({Address}, {internal id}, VALUE(@cell) = [Property ID2]@row), 1)


    This converts the number in the Internal ID column to be a Value then compares it to the Property ID2 column. Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Ron Anderson

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What exactly is your existing INDEX/COLLECT formula?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ron Anderson
    Ron Anderson ✭✭✭✭

    =INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row), 1)

    The formula works when the value in internal id is a numeric value entered by me. (text/number). As soon as I change it to auto number it fails.


    Andre. Sharing sheets with you.

  • Hi @Ron Anderson

    The Invalid Value error is indicating that the formula is unable to find a match between your two columns. Is your Property ID2 column a Text/Number or an Auto-Number? I would expect that the two columns will need to be the same type.

    Or, if you are only using numbers in your Auto-Number column, they could be seen as text. Try adding "" to your Property ID2 cell, like so:

    =INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row + ""), 1)

    If that doesn't work, it could be that the value in your Property ID2 column is being seen as text, try adding VALUE around it:

    =INDEX(COLLECT({Address}, {internal id}, VALUE([Property ID2]@row)), 1)

    Let us know if any of this worked!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ron Anderson
    Ron Anderson ✭✭✭✭

    I tried =INDEX(COLLECT({Address}, VALUE({internal id}), [Property ID2]@row + ""), 1)

    and INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row + ""), 1)

    and INDEX(COLLECT({Address}, {internal id}, value([Property ID2]@row)), 1)

    none work.

    You are correct. I think the auto number field [internal id] is text and the value in Property ID2 is a number which is causing the issue. Any thoughts?

  • Hi @Ron Anderson

    Are you able to post screen captures of these two columns? (Blocking any sensitive data). I can't seem to replicate the error. It would be helpful to also see a screen capture of the Column Properties for your Auto-Number column.

    Thanks!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ron Anderson
    Ron Anderson ✭✭✭✭

    I can share both sheets. Nothing sensitive in them. Send email

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Ron Anderson

    I was able to replicate what you're seeing! If your referenced column is being recognized as text, you can adjust your formula to find the value like so:


    =INDEX(COLLECT({Address}, {internal id}, VALUE(@cell) = [Property ID2]@row), 1)


    This converts the number in the Internal ID column to be a Value then compares it to the Property ID2 column. Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ron Anderson
    Ron Anderson ✭✭✭✭

    awsome. that fixed it. Thanks

  • No problem! I'm glad we could figure it out.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!