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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!