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.
Best Answer
-
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
-
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.
-
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!
-
=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.
-
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 -
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?
-
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 -
I can share both sheets. Nothing sensitive in them. Send email
-
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 -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!