Concatenate Displays "0" for Blank....But Not Always

Steve Kopischke

I am concatenating fields (only one is filled per row) so that I can use that result in the calendar view. I added a new field to the concatenation ([column one name]@row + [column two name]@row....) this morning and the new row is pushing a "0" (zero) into the concatenation when blank. None of the other blank fields is pushing a zero. All fields used in the concatenation are formatted as Dropdown (single select).

Here is the concatenation formula I am using: =[Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row

The column "Ajera Pod" is the one pushing the 0 into the display field. This is occurring in every row.

What are my troubleshooting steps?

Jeff Reisman

Use JOIN instead. JOIN acts as if any value, even blanks, are text and not numbers. Your formula above is trying to add all the blanks together as possible integers. I know, it's weird. The below will leave a blank cell if all the input cells are blank:

 =JOIN([Ajera Pod]@row:[Vision Pod]@row)

This assumes your columns are all in order left to right and that's how you want them displayed. You can add a delimiter between the values as well:

 =JOIN([Ajera Pod]@row:[Vision Pod]@row, " - ")

If you need them in a different order, then use + in between the field names with JOIN:

=JOIN([Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row)

Steve Kopischke

Also - 0 is not a valid entry in any of the columns.

Jeff Reisman

Use JOIN instead. JOIN acts as if any value, even blanks, are text and not numbers. Your formula above is trying to add all the blanks together as possible integers. I know, it's weird. The below will leave a blank cell if all the input cells are blank:

 =JOIN([Ajera Pod]@row:[Vision Pod]@row)

This assumes your columns are all in order left to right and that's how you want them displayed. You can add a delimiter between the values as well:

 =JOIN([Ajera Pod]@row:[Vision Pod]@row, " - ")

If you need them in a different order, then use + in between the field names with JOIN:

=JOIN([Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row)

Steve Kopischke

@JReisman27: Interesting result. I edited the formula as you recommended and got an error. Then I copied/pasted the formula as you typed it above. I also removed the first column (the one that has been giving me trouble). All of those attempts result in #INCORRECT ARGUMENT SET.

I need to look into the JOIN command more closely.

Steve Kopischke

AHA!

=JOIN([First Column]:[Last Column])

....is the correct formula to use.

Jeff Reisman

@Steve Kopischke - Interesting. It works fine for me:

These are single-select fields with various data types in the choices.

Clicking into my formula field shows it's targeting the correct cells:

What does yours show?

I have encountered glitches where Smartsheet does not pick up the correct targeting until I type the formula by hand and let it create its own pointers to the referenced cells as I go.

Jeff Reisman

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

Steve Kopischke

The original formula was working fine until I added the "Ajera Pod" column this morning. If I remove the column, the formula works.

When I changed from the original to JOIN, I edited the formula with the keyboard, then copy/paste. If I removed the new column, it still wouldn't work. It works with the first and last columns bracketed by the ":" character.

Jeff Reisman

OK - try this one:

=JOIN([Ajera Pod]@row) + JOIN([ConceptShare Pod]@row) + JOIN([Costpoint Pod]@row) + JOIN([Talent DLZ Pod]@row) + JOIN([Talent DTM Pod]@row) + JOIN([Vantagepoint Pod]@row) + JOIN([Vision Pod]@row)

The individual JOINs turn each cell into text.

Ami Veltrie

@JReisman27 I have been looking for this exact solution! Thank you! 🏅