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

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?

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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?

  • Genevieve P.
    Genevieve P. Employee Admin

    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!

  • Ron Anderson
    Ron Anderson ✭✭✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin
    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

  • Ron Anderson
    Ron Anderson ✭✭✭✭

    awsome. that fixed it. Thanks

  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!