How can I get rid of blanks when using join function pulling in from another Sheet?

aalang103696

Currently I have a column in a timeline sheet with the formula ==(IF(AND(Milestones@row = 1, [% Complete]@row < 0.1), Finish@row + " : " + [Task Name]@row, " ")) That says if Milestone column is checked, and Complete column is less than 100%, then combine Finish Date and Task Name with a : in between. I am wanting to JOIN this column into one cell in another sheet (Status Log), but without the blanks. I have this formula =JOIN({WEI-1376 - HT - Dairy New Formula Roun - T Range 4}, CHAR(10)) to pull this column in and separate the lines, however, it is pulling in the blanks making the cell really long instead of combining them together without blank in-between.

I have tried the Collect/Join formula, but it doesn't seem to work either, I keep getting #UNPARSEABLE. =JOIN(COLLECT({Join Date & Task}, {Milestone}, = 1, {% Complete}, < 0.1){Milestone Reporting}, CHAR(10) @cell < > "")

Screen shots attached.

Paul Newcome

Try this:

=JOIN(COLLECT({Join Date & Task}, {Join Date & Task}, @cell <> "", {Milestone}, = 1, {% Complete}, < 0.1, {Milestone Reporting}), CHAR(10))

Jeff Reisman

@aalang103696

Why do you have the CHAR(10) where you have it in this formula?

=JOIN(COLLECT({Join Date & Task}, {Milestone}, = 1, {% Complete}, < 0.1){Milestone Reporting}, CHAR(10) @cell < > "")

It's like it's sitting right in the middle of the second criteria range / criteria pair. And shouldn't the {Milestone Reporting}, @cell <> "" be inside the COLLECT?

Try this one. The logic SHOULD be "collect the values from the Join Date & Task column where the Milestone is checked, % Complete is less than 10%, and where Milestone Reporting is not blank, and join them with a carriage return between values. This should exclude any row where Milestone Reporting is blank. (Make sure the color-coding on the parentheses matches - the open parentheses after JOIN should be the same color as the very last close parentheses.)

=JOIN(COLLECT({Join Date & Task}, {Milestone}, 1, {% Complete}, < 0.1, {Milestone Reporting}, NOT(ISBLANK(@cell))), CHAR(10))

aalang103696

https://community.smartsheet.com/discussion/comment/339215#Comment_339215

I took out the NOT(ISBLANK(@cell) and tried replacing with @cell < > "".. I think I just had a few things in the wrong order, which is why CHAR(10) was in the middle.. not end. I ended up making the following formula work for what I was looking to do: =JOIN(COLLECT({Milestone Reporting}, {Milestone}, 1, {Milestone Reporting}, NOT(ISBLANK(@cell))), CHAR(10))

I only needed to use one of the columns Milestone Reporting OR Join Date & Task in the formula. I had created 2 different columns with 2 different formulas to try to use JOIN formulas different on the other sheet... of course neither worked in what I was doing.

The formula you provided gave me a #REF in return. so I modified it to use Milestone Reporting column only.

Thanks for your help!!

Ashley

aalang103696

https://community.smartsheet.com/discussion/comment/339206#Comment_339206

I only needed to use one of the columns Milestone Reporting OR Join Date & Task in the formula. I had created 2 different columns with 2 different formulas to try to use JOIN formulas different on the other sheet... of course neither worked in what I was doing.

The formula you provided gave me a #UNPARSEABLE in return. So I modified Jeff's formula: =JOIN(COLLECT({Join Date & Task}, {Milestone}, 1, {% Complete}, < 0.1, {Milestone Reporting}, NOT(ISBLANK(@cell))), CHAR(10)) to look at the one column.

Thanks for your help!!

Ashley