Google-sheets – Multiple IF conditions nested with a CONCATENATE

concatenategoogle sheetsworksheet-function

I have a formula as follows which I'm using for a scheduling system within Google Sheets:

=IF(B2="","",(CONCATENATE($B$1&" "&B2&CHAR(10)&$C$1&" "&C2&CHAR(10)&$D$1&" "&D2&CHAR(10)&$E$1&" " &E2&CHAR(10)&$F$1&" " &F2&CHAR(10)&$G$1&" " &G2)))

Currently, my formula works b2 has a value inside it which is great, what I want, however, is for the formula only to show if one value is inside either.

B2, C2, D2, E2, F2 or G2.

so if c2 has a value I want the formula to parse.

I've tried

=IF(B2,C2,E2) etc with no luck. 

I've also tried:

=IF(OR(B2="",C2="") which parsed the formula but kept it visible even with no data.

Reason for this is that I pull these fields into a master schedule and I only want it to show when one of the fields is populated if that makes sense? otherwise, the schedule will look far to busy.

https://docs.google.com/spreadsheets/d/1KE3VOI43M4-QlWB0EZldCqR73d3RHDnRnUNlv1MqLMo/edit?usp=sharing

Document for you guys.

Best Answer

here it is:

=IF(A2<>"",CONCATENATE(
                   IF(B2<>"",$B$1&": "&B2&CHAR(10),),
                   IF(C2<>"",$C$1&": "&C2&IF(OR(D2<>"",E2<>"",F2<>"",G2<>""),CHAR(10),),),
                   IF(D2<>"",$D$1&": "&D2&IF(OR(       E2<>"",F2<>"",G2<>""),CHAR(10),),),
                   IF(E2<>"",$E$1&": "&E2&IF(OR(              F2<>"",G2<>""),CHAR(10),),),
                   IF(F2<>"",$F$1&": "&F2&IF(                        G2<>"", CHAR(10),),),
                   IF(G2<>"",$G$1&": "&G2,)),)