COLLECT function to reference another sheet

OshaK

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!

Paul Newcome

You need to follow the appropriate steps for creating cross sheet references to be able to refrence data in another sheet.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.

OshaK

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,

Paul Newcome

You would simply select those cells when creating the reference instead of clicking on a column header.

OshaK

@Paul Newcome

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 like

1-2: : Conference

5-15: PTO

25, 20: Vacation

where the second column has a multi drop down list with Time off reasons.

Thank you!