can anybody help with this join(collect) issue?
I created a join(collect) to gatehr all the permits that match the criteria, in this case same account.
The search column [permit type] is looking at a different sheet to find the right permit type.
The join(collect) in column [Number_permits] is working perfectly, that column is capturing all permit numbers of the same account.
I just don't understand why I get a #NO MATCH. I tried so many tricks and I just can't get it to work, what am I missing here?
Looking forward to any response. Cheers
Hilbert
Best Answer
-
What are the column types for all the columns - Permit type, Number, Number_Permits, Multiple_Permits_PTW, Account?
Can we see a screenshot of the formula that's working for the Number_Permits column?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
In your Permit Type column, are any cells showing error from the lookup formula? When referencing a formula column from another formula, errors in any cell in that formula column can cause problems.
If there are, you should wrap that formula in IFERROR to suppress error messages and replace them with blanks.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, Thx for your support, there are no error messages in any of the columns that I'm referencing to.
-
What are the column types for all the columns - Permit type, Number, Number_Permits, Multiple_Permits_PTW, Account?
Can we see a screenshot of the formula that's working for the Number_Permits column?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
The formula for Number_permits is below
Accept account (that is a dropdown, all columns are text/number columns.
The number column (is a text/number) with the following formula.
The permit type column (type/number) has a formula that links to another sheet.
The link to external sheet has translations that I'm using in this sheet.
The search column (text/number) has this formula
The language column (dropdown list, restrict to list only)
The PTW_type- column (Dropdown list, restrict to list only)
To me this is all valid and no errors appear. I just can't get my head around why I'm getting a No Match while it is ticking all the boxes .
I'm pretty experienced with smartsheet and I have solved a lot of challenges but this just doesn't make sense.
I hope you or anybody who is also looking at this can see what I'm doing wrong.
Much appreciated to support me. Have a nice weekend.
-
Hi Jeff, with shame I must admit I missed an error in a row which caused this, and another lesson learned, thanks for your support.
-
@Hilbert Kok no problem at all!
Let me ask you a question: How do you have icons for column names? Is that something particular to EU Region Smartsheet? Because that doesn't seem to be an option here in the USA.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I hope you're well and safe!
It works on both my EU and US instances, so it should work for you.
You'd add an Emoji. Doesn't that work?
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 used Emoji as the name of the columns to use the sheet internationally,
On your keyboard you click on FN or 🌐 and this brings up the emoji field
Just select an emoji and it works thru out smartsheet with formula's etc.
-
@Andrée Starå @Hilbert Kok That's wild, I had no idea that would even be a thing!
I had to right click on the Windows task bar, select "Show touch keyboard button" and then click on that keyboard button to bring up the screen keyboard:
And now I have a column called Steak Shrimp Donut! Fantastic and hilarious!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Then this was a productive day, and you learned something new! ;)
I always say it's a good day when you learn something new!
You can use this page and copy from as well.
getemoji.com
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
