How to get JOIN working when referencing another sheet?

pdv90
pdv90 ✭✭✭
edited 06/12/23 in Formulas and Functions

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}, " - ")

Answers

  • Andrée Starå
    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!

    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.

  • pdv90
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!