Formula to return tab name if specific value is located within row

[Darren_workforce]

I have 5 tabs for each weekday, MON, TUE, WED, THU, FRI. In the tab, there are 1s if an employee works that day and 0s if they do not.

In tab AGENT, I have a list of all employees down Column B with empty cells in Column C in which I would like to set the formula. Is it possible to create a formula that does the following?

Formula in C2 would do 3 things: 1) Reference B2 for the employee name, 2) check all 5 workday tabs for the John Smith row, and then 3) if John Smith has a 1 in the adjacent cell, the formula would illustrate that he does work that day by populating C2 with M,T,W,T,F (of course, the first letter of each of the 5 tabs). If John Smith does not work on Wednesday, the formula would display MTTF, etc.

Thank you very much in advance!!

 

[Eric W]

How about:

Book1
	A	B	C
1
2		John Smith	Mon,Tue,Wed,Thu,Fri
3		Brad Cooper	Wed,Thu,Fri
4		Mary Bradley	Wed,Fri
Agent

Cell Formulas
Range	Formula
C2:C4	C2	=LET(dow,{"Mon","Tue","Wed","Thu","Fri"},TEXTJOIN(",",1,IF(COUNTIFS(INDIRECT(dow&"!A:A"),B2,INDIRECT(dow&"!B:B"),1),dow,"")))

This only checks column B for the 1. I wasn't sure if I needed to check C or D.

 

[Darren_workforce]

How about:

Book1
	A	B	C
1
2		John Smith	Mon,Tue,Wed,Thu,Fri
3		Brad Cooper	Wed,Thu,Fri
4		Mary Bradley	Wed,Fri
Agent

Cell Formulas
Range	Formula
C2:C4	C2	=LET(dow,{"Mon","Tue","Wed","Thu","Fri"},TEXTJOIN(",",1,IF(COUNTIFS(INDIRECT(dow&"!A:A"),B2,INDIRECT(dow&"!B:B"),1),dow,"")))

This only checks column B for the 1. I wasn't sure if I needed to check C or D.

It appears to do the job. There are additional columns I didn't think to add to my question. The C and D could theoretically have a 1 if an agent does come in later to work. All the way to Col R. I tried to adjust your formula. However it returned the #VALUE error.

 

[Eric W]

Looking at other columns actually ramps up the difficulty level quite a bit. You could do something like this:

Book4
	A	B	C
1
2		John Smith	Mon,Tue,Wed,Thu,Fri
3		Brad Cooper	Tue,Thu
4		Mary Bradley	Mon,Tue,Wed,Fri
Agent

Cell Formulas
Range	Formula
C2:C4	C2	=LET(dow,{"Mon","Tue","Wed","Thu","Fri"},ma,MATCH(B2,Mon!$A:$A,0),mb,MATCH(B2,Tue!$A:$A,0),mc,MATCH(B2,Wed!$A:$A,0),md,MATCH(B2,Thu!$A:$A,0),me,MATCH(B2,Fri!$A:$A,0),sa,SUM(INDEX(Mon!$B:$R,ma,0)),sb,SUM(INDEX(Tue!$B:$R,mb,0)),sc,SUM(INDEX(Wed!$B:$R,mc,0)),sd,SUM(INDEX(Thu!$B:$R,md,0)),se,SUM(INDEX(Fri!$B:$R,me,0)),c,CHOOSE({1,2,3,4,5},sa,sb,sc,sd,se),TEXTJOIN(",",1,IF(c,dow,"")))

I came up with a shorter formula, but it was more complicated. There was a short but noticeable lag when changing data, and I only had 3 rows. It would probably be worse for more rows. It's also possible that some of the newer functions such as HSTACK might simplify things, but I don't have those yet to play with. Maybe someone else could chime in.

 

[Darren_workforce]

Looking at other columns actually ramps up the difficulty level quite a bit. You could do something like this:

Book4
	A	B	C
1
2		John Smith	Mon,Tue,Wed,Thu,Fri
3		Brad Cooper	Tue,Thu
4		Mary Bradley	Mon,Tue,Wed,Fri
Agent

Cell Formulas
Range	Formula
C2:C4	C2	=LET(dow,{"Mon","Tue","Wed","Thu","Fri"},ma,MATCH(B2,Mon!$A:$A,0),mb,MATCH(B2,Tue!$A:$A,0),mc,MATCH(B2,Wed!$A:$A,0),md,MATCH(B2,Thu!$A:$A,0),me,MATCH(B2,Fri!$A:$A,0),sa,SUM(INDEX(Mon!$B:$R,ma,0)),sb,SUM(INDEX(Tue!$B:$R,mb,0)),sc,SUM(INDEX(Wed!$B:$R,mc,0)),sd,SUM(INDEX(Thu!$B:$R,md,0)),se,SUM(INDEX(Fri!$B:$R,me,0)),c,CHOOSE({1,2,3,4,5},sa,sb,sc,sd,se),TEXTJOIN(",",1,IF(c,dow,"")))

I came up with a shorter formula, but it was more complicated. There was a short but noticeable lag when changing data, and I only had 3 rows. It would probably be worse for more rows. It's also possible that some of the newer functions such as HSTACK might simplify things, but I don't have those yet to play with. Maybe someone else could chime in.

Thank you very much Eric! That did it!! Such a long and complex formula I'd have never done it alone. Thank you again!!