Concatenate Displays "0" for Blank....But Not Always
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?
Best Answer
-
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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Also - 0 is not a valid entry in any of the columns.
-
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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
-
AHA!
=JOIN([First Column]:[Last Column])
....is the correct formula to use.
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
#INCORRECT ARGUMENT SET
Cause
This error is presented under the following circumstances:
- For functions that take two ranges: The range sizes don’t match for the function.
- The function is missing an argument.
- There is an extra function in the argument.
Resolution
Correct the range size or arguments, adding or removing arguments in the formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@JReisman27 I have been looking for this exact solution! Thank you! 🏅
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!