Formula with CHAR(10) and multiple references
hello,
I am working on a formula to combine all notes from multiple PMO discussion groups. I have it working but in my Master notes column if a discussion group left information blank, the CHAR(10) puts large blank space within the column. Each discussion group is grouped together within the below formula.
=JOIN(COLLECT([PMO RB]@row + CHAR(10) + [PMO Review Prelim Meeting Date]@row + CHAR(10) + [PMO Review Board Comments]@row + CHAR(10) + Scheduling@row + CHAR(10) + [Scheduling Review Date]@row + CHAR(10) + [Scheduling Committee Comments]@row + CHAR(10) + Clinical@row + CHAR(10) + [Clinical Discussion Group Date]@row + CHAR(10) + [Clinical Discussion Group Decision]@row + CHAR(10) + [Clinical Group Comments]@row + CHAR(10) + [BA name for comments]@row + CHAR(10) + [Business Discussion Group Date]@row + CHAR(10) + [Business Discussion Group Decision]@row + CHAR(10) + [Business Group Comments]@row + CHAR(10) + Analytics@row + CHAR(10) + [Analytics Discussion Group Date]@row + CHAR(10) + [Analytics Discussion Group Decision]@row + CHAR(10) + [Analytics Group Comments]@row + CHAR(10) + Security@row + CHAR(10) + [Security Discussion Group Date]@row + CHAR(10) + [Security Discussion Group Decision]@row + CHAR(10) + [Security Group Comments]@row + CHAR(10) + Technical@row + CHAR(10) + [Technical Discussion Group Date]@row + CHAR(10) + [Technical Discussion Group Decision]@row + CHAR(10) + [Technical Group Comments]@row + CHAR(10) + Enterprise@row + CHAR(10) + [Enterprise Steer Review Date]@row + CHAR(10) + [Enterprise Steer Decision Requested]@row + CHAR(10) + [Enterprise Comments]@row, [Security Group Comments]@row + [Business Group Comments]@row + [Analytics Group Comments]@row + [Technical Group Comments]@row + [Clinical Group Comments]@row + [Enterprise Comments]@row, NOT(ISBLANK(@cell))), "-")
Answers
-
Hi @Erin Kim - I am showing this with just four of your columns. This assumes that all of your in-scope columns are next to each other:
=JOIN(COLLECT([PMO RB]@row:Scheduling@row, [PMO RB]@row:Scheduling@row, NOT(ISBLANK(@cell))), CHAR(10))
-
Use if statements with each one of your notes and include the char(10) in the if statement. Also, I don’t think you need to use join collect here.
=if([PMO RB]@row = "", "", [PMO RB]@row + CHAR(10)) + if[PMO Review Prelim Meeting Date]@row = "", "", [PMO Review Prelim Meeting Date]@row + CHAR(10)) + if([PMO Review Board Comments]@row = "", "", [PMO Review Board Comments]@row)
Repeat for every entry. Hope this helps!
-
@Scott Peters My columns are not next to each other, they are sprinkled throughout my sheet :) Thanks for the insight!
-
@Erin Kim What was the resolution you found for this? I am having the exact same issue and my initial formula looked very similar to yours.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!