How to get JOIN working when referencing another sheet?

I'm using the JOIN formula, if I put the formula on the same sheet as my column references, it works just fine, however when I move this to a separate sheet and update the column references to the other sheet I get the error #UNPARSEABLE.

I haven't removed any of the formula delimiters only which sheet I'm referencing.

Any ideas on why I'm unable to get this to work cross-sheets would be greatly appreciated: Example of Formula when contained in 1 sheet:

=JOIN([Deployed Location - Office/Project]@row:[Deployed Location - Room Number]@row, " - ")

Example of Formula referencing separate sheet:

=JOIN({Sheet - Coolab/CoolFx/TC/CH4 Inventory Range 1}:[Sheet - Coolab/CoolFx/TC/CH4 Inventory Range 2}, " - ")

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

Andrée Starå

Hi @pdv90

I hope you're well and safe!

You can add a so-called helper column that JOIN the columns in the other sheet, and then you'd collect it back to the main sheet.

Make sense?

Did that work/help?

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!

pdv90

Hi Andree,

Thanks for your reply.

In fact I was already working with the helper column. I wanted to know why the JOIN function doesn't work on it's own and if I was doing anything wrong.

Thank you again!

Hope you have an amazing week yourself.

pdv90

I'm just linking the cell to the corresponding cell.

Haven't used Collect function.

To my understanding Collect has to be used within another function. Here I just need the alphanumeric values in one cell(the helper cell) to be replicated in another cell which is in another sheet.

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.