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

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366724#Comment_366724

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?

All comments

Jeff Reisman

@Hilbert Kok

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.

Hilbert Kok

Hi Jeff, Thx for your support, there are no error messages in any of the columns that I'm referencing to.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/366724#Comment_366724

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?

Hilbert Kok

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.

Hilbert Kok

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.

Jeff Reisman

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

Andrée Starå

Hi @Jeff Reisman

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!

Hilbert Kok

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.

Jeff Reisman

@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!

Andrée Starå

@Jeff Reisman

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.