INDEX COLLECT formula error with criteria that starts with zero
I have a bill of materials sheet that will pull in the cost of an item based on the model number, using an INDEX COLLECT formula referencing a separate pricing catalog sheet. All works well, except for when a model number is an integer that starts with a zero, and Smartsheet automatically adds a "hidden" apostrophe to the beginning of the model number. For example, when entering a model number of 01234, Smartsheet automatically changes it to '01234 then returns an #INVALID VALUE error message in the item cost cell that contains the INDEX COLLECT formula. I've made sure that the model number columns in both the bill of materials sheet and the separate pricing catalog sheet are both Text/Number type columns, and the model numbers on both sheets start with the "hidden" apostrophe, but still no luck. How can I make this work properly? Thanks.
Answers
-
Insert a helper column on the source sheet that has this in it:
=[Model Number]@row + ""
This will convert everything to a text value so that you have the same data TYPE across all entries.
Then use the + "" bit in your MATCH function as well.
=INDEX({Cost Column}, MATCH([Model Number]@row + "", {Helper Column}, 0))
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!
-
Hi Paul. Thanks for the quick response. I probably should have included this info: I'm using an INDEX COLLECT formula (rather than INDEX MATCH) because I have multiple criteria from the bill of materials that are being matched to the pricing catalog sheet. I'm matching both the manufacturer and the model number. Manufacturer is always text, but it's the model number that can be an integer starting with zero which causes my issues. I'm still having trouble using your proposed solution using the INDEX COLLECT formula. Is there another method?
-
You would use it very similarly.
=INDEX(COLLECT({Range To Pull}, {Manufacturer}, @cell = Manufacturer@row, {Helper Column}, @cell = [Model Number]@row + ""), 1)
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!
-
Hi Paul. Unfortunately, still no luck. If the model number is strictly an integer, I get an #INVALID VALUE error message. That is now happening with integers that do and don't start with a zero. When I change the model number to be text, or even a combination of integer and text, then my INDEX COLLECT formula works well. Any idea why that's happening? I really appreciate the help with this.
-
Have you tried using the VALUE function inside the COLLECT one?
COLLECT(VALUE(cell in question))
-
Hi Bill. Thanks for the suggestion, but doesn't look like the VALUE function will work for me. Some of my model numbers are integers, and some are text.
-
I am having this issue as well, has a solution been found?
-
Unfortunately, no. I haven't been able to find a solution that works. Please let me know if you do. Thanks.
-
Hi @Mike Tomei
I hope you're well and safe!
Can you share a screenshot of how it looks?
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, Awesome, 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.
-
I've just come across this issue in a build and have worked around it by adding helper columns to both the source and target sheets that adds a set prefix (I've used an X) to the column with the preceding zeros. I've then pointed the COLLECT function to the helper column. It's not perfect, but it got the job done.
Phil Robbins
-
I'm having this issue but the source sheet isn't owned by my organization so I cannot create a helper column in it. Is there any known solution where one doesn't need to create a helper column?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!