How to create text in a cell based on the values of multiple cells

[grfbro1]

Hello - I'm trying to create a sentence in a cell based on the values in other cells in a different worksheet. On one worksheet is the picture below where the entries are made and i can put in yes or no.

Based on the entries to theses cells, I want to create a sentence below on a separate worksheet:

NOTES: Vacation, Sick, Medical, Reporting, and Mercury will be billed as incurred.

If the user has a no in the cell, than that word will not be in the sentence. The user can only select yes or no and all the yes answers will show the cell above in the sentence with a , separating the words.

 

[iggydarsa]

=IF(CONCAT(IF(B2="Yes",B1&", ",""),IF(C2="Yes",C1&", ",""),IF(D2="Yes",D1&", ",""),IF(E2="Yes",E1&", ",""),IF(F2="Yes",F1&", ",""),IF(G2="Yes",G1&", ",""))="","Nothing",LEFT(CONCAT(IF(B2="Yes",B1&", ",""),IF(C2="Yes",C1&", ",""),IF(D2="Yes",D1&", ",""),IF(E2="Yes",E1&", ",""),IF(F2="Yes",F1&", ",""),IF(G2="Yes",G1&", ","")),LEN(CONCAT(IF(B2="Yes",B1&", ",""),IF(C2="Yes",C1&", ",""),IF(D2="Yes",D1&", ",""),IF(E2="Yes",E1&", ",""),IF(F2="Yes",F1&", ",""),IF(G2="Yes",G1&", ","")))-2)&" will be billed as incurred")

this is the formula to concatenate the verbiage but more data is needed on the criteria of showing the data on other worksheets

 

[grfbro1]

That worked perfect. Thanks and quite the long formula.

 

[iggydarsa]

Glad to hear.
You might wanna mark as a solution for future researchers.

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

That worked perfect.

It doesn't seem to quite give the same result as your example in post 1. However, if that is the result you want, then you could also try this much shorter & more easily extendable version.

23 08 01.xlsm
	B	C	D	E	F	G	H
1	a	b	c	d	e	f
2	Yes	Yes	Yes	Yes	Yes	No	a, b, c, d, e will be billed as incurred
3	No	No	No	No	No	No	Nothing
4	No	Yes	No	Yes	No	Yes	b, d, f will be billed as incurred
5							Nothing
6	No	No	Yes	No	No	No	c will be billed as incurred
grfbro1

Cell Formulas
Range	Formula
H2:H6	H2	=LET(t,TEXTJOIN(", ",1,FILTER(B$1:G$1,B2:G2="Yes","")),t&IF(LEN(t)," will be billed as incurred","Nothing"))

 

[Peter_SSs]

I want to create a sentence below on a separate worksheet:

I forgot about that when providing my previous response. The adjustment would be something like this.

grfbro1.xlsm
	A	B	C	D	E	F	G
1		a	b	c	d	e	f
2		Yes	Yes	Yes	Yes	Yes	No
3		No	No	No	No	No	No
4		No	Yes	No	Yes	No	Yes
5
6		No	No	Yes	No	No	No
Sheet1

grfbro1.xlsm
	A
1
2	a, b, c, d, e will be billed as incurred
3	Nothing
4	b, d, f will be billed as incurred
5	Nothing
6	c will be billed as incurred
Sheet2

Cell Formulas
Range	Formula
A2:A6	A2	=LET(t,TEXTJOIN(", ",1,FILTER(Sheet1!B$1:G$1,Sheet1!B2:G2="Yes","")),t&IF(LEN(t)," will be billed as incurred","Nothing"))