Help with Cross Sheet JOIN(COLLECT) formula

Megan LaFrance

I've built a formula to complete a JOIN(COLLECT). 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.

From what I can tell 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(COLLECT([POC Status]:[POC Status], [POC System]:[POC System], "RelOne_POC1"), ",")

Example of Formula referencing separate sheet:

=JOIN(COLLECT({POC Client Tracking Range 1}:{POC Client Tracking Range 1},{POC Client Tracking Range 2}:{POC Client Tracking Range 2}, "RelOne_POC1"), ",")

Paul Newcome

When referencing another sheet, you do not use the colon and second reference.

Try this instead...

=JOIN(COLLECT({POC Client Tracking Range 1},{POC Client Tracking Range 2}, "RelOne_POC1"), ",")

Paul Newcome

When referencing another sheet, you do not use the colon and second reference.

Try this instead...

=JOIN(COLLECT({POC Client Tracking Range 1},{POC Client Tracking Range 2}, "RelOne_POC1"), ",")

Megan LaFrance

That works perfectly. Thank you very much for the help!

Paul Newcome

Happy to help! 👍️