Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Feature Request: JOIN argument range
Slightly related to:
JOIN appears to be "vertically-centric", that is, designed more for joining text from a vertical range instead of a horizontal one.
I would like to cherry pick cells from the current row to JOIN into a longer string.
For example, forming a combined address from 4 different columns.
Unfortunately (imo), the JOIN command does not seem to let me develop the range of concern.
Easy if the four columns are contigous:
=JOIN([Address1]23:[Zip]23,"+")
but not so if they are not.
Is there some way to get the non-contiguous columns into the range?
If not, I would like to.
Craig
Comments
-
Hi Craig,
I do it like this.
=[Job Number]6 + "," + [Assigned To]6 + "," + [Assign To 2]6
These columns are not next to each other. This make it so I can see the Job Number and Descirption [Job Number] and the 2 people I have assigned to that job all in 1 column. I do this so I can see it all together on the calendar and gantt.
Peace.
-
Thanks Heidi
That's the way I used to do it.
The doc on the new feature says:
NOTE: This can be a good alternative to using + (the plus sign) to join together text strings.
By "good alternative" (emphasis mine), I'm hoping they mean "optimized for speed".
I need to always keep in mind:
1. cell count
2. conditional formatting rules
3. "complex" formulas
as these slow things down, sometimes to be unusable depending on which hotel I happen to be staying in.
Craig
-
Join and direct concatenation should be similar in speed for you, so I wouldn't worry about perf in this case. Join is handy for contiguous ranges and required for collections that aren't represented in cells (like =ancestors()) but it should take about the same amount of time to compute as directly referencing each cell.
Cheers,
Erik
-
I submitted the suggestion that we be able to address these new "ranges" or arrays found in these new functions via a parent-child relationship. This way, you could address whatever columns you wanted, and when the lookup dataset grows or shrinks as it needs to change, as long as the child relationship remains intact, you don't have to resize your lookup, which is kind of nasty.
