Feature Request: JOIN argument range

J. Craig Williams

Slightly related to:

https://community.smartsheet.com/discussion/release-2016-08-06-formula-argument-declaration-confusion

 

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

 

 

 

 

 

Heidi Decker

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.

J. Craig Williams

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

 

Erik Rucker

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

John Sauber

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.