COLLECT function to reference another sheet
Hello,
this formula works great within the same sheet:
="January" + " " + JOIN(DISTINCT(COLLECT(Helper5:Helper35, Helper5:Helper35, <>"")), ",")
and produces this output:
I need to make it work in another sheet referencing the original one to produce exactly the same output in the new sheet (I just need to separate it for another project and better display.). But if i move it as is, it give me UNPARSEABLE.
I've tried multiple things but no luck. The data comes from a single "Helper" column but different Row Numbers based on a Month.
thank you for your help!
Answers
-
You need to follow the appropriate steps for creating cross sheet references to be able to refrence data in another sheet.
.
-
Hi @Paul Newcome thank you, what i can't find in any of the community topics is how to specifically reference a range of several rows, not the entire column.
I've tried many variations of
=JOIN(DISTINCT(COLLECT({Helper}, 5, 35, <>"")), ",")=JOIN(DISTINCT(COLLECT({Helper}, <>"")), 5, 35, ",")
=JOIN(DISTINCT(COLLECT({Helper}, <>"")), 5-35, ",")
- to reference rows 5 thru 35
in a different sheet but can't seem to get the syntax right. The tutorials and formulas examples don't go into that level of details, but maybe I'm just not finding it.
and this one
=JOIN(COLLECT({helper 3:33}, @cell <> ""), ", ")
returns INCORRECT ARGUMENT SET. This last formula was generated by SS AI
If you have any suggestions for me, I'd appreciate it.
Thank you,
-
You would simply select those cells when creating the reference instead of clicking on a column header.
-
Thank you, Paul.
Chat GPT gave me the formula that is working:
=JOIN(COLLECT({Helper Data}, {Helper Data}, <> ""), ", ")
However, how do i add the second criterion (from the second column) to this formula?
i need the final result to be something like1-2: : Conference
5-15: PTO
25, 20: Vacation
where the second column has a multi drop down list with Time off reasons.
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!