Google Sheets – How to Use Multiple IF Conditions with 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:

https://docs.google.com/spreadsheets/d/

=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,)),)

Related Solutions

Google Sheets – Calculate Start and End Row Number of First Non-Empty Range

My attempt (there may well be a shorter way):

For startFirstRange, lets say invoked in A1:

=INDEX(IFERROR(FILTER(ROW(Sheet1!B2:B),NOT(ISBLANK(Sheet1!B2:B))),-1),1)

Then for endFirstRange, which will reference the result of startFirstRange in A1:

=IF(A1=-1,0,IFERROR(INDEX(FILTER(ROW(Sheet1!B2:B)-1,ROW(Sheet1!B2:B)>A1,ISBLANK(Sheet1!B2:B)),1),ROWS(Sheet1!B:B)))

Google-sheets – Formula result depending on the week of the day

This can be solved by introducting a separate sheet, let's call it 'Weekdays':

| Weekday | Number of stylists Site 1 | Number of stylists Site 2 | Number of stylists Site 3 |
|       1 |                         0 |                         0 |                         0 |
|       2 |                         0 |                         0 |                         0 |
|       3 |                         5 |                         3 |                         5 |
|       4 |                         4 |                         4 |                         4 |
...

The first column is the weekday (0 is Sunday, 1 is Monday, etc), the next columns are the number of stylists available on that weekday, on the different sites.

Having this table, I can use a formula to match a weekday + a site with a number of stylists, from the main sheet. The following formula is for Tuesdays on Site 1:

=VLOOKUP(B$2, Weekdays!$A$2:$D$8,2)

The VLOOKUP function (check out its documentation) takes the value from B2 (Tue, which translates to 2), and matches it against the first column in the Weekdays sheet. When it finds a matching row, it returns the 2^nd column - which is the Number of stylists Site 1.

Thus, the formula can be dragged across the entire row for Site 1, and it will adjust automatically.

For the other sites, you need to modify the last parameter of the formula. For Site 2, replace 2 with 3: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,3), and for Site 3, replace with 4: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,4)

I have set up an example spreadsheet to demonstrate this, feel free to copy it.

Best Answer

Related Solutions

Google Sheets – Calculate Start and End Row Number of First Non-Empty Range

Google-sheets – Formula result depending on the week of the day

Related Topic