If specific text within multiple ranges, input corresponding cell values

[CallMeKris]

Hello!

I'm trying to set up a monthly sign up sheet for my work. The people who will be using it have a tendency to break excel files so I want to keep what they can do limited to just selecting options from drop down menus. On the weekly tables, they will select their name from the list for which day and time works for them and what I want to happen is the date and time slot they chose to then be displayed in the blue table. I've tried a few various IF formulas but I'm no pro by any means and all my googling hasn't gotten me anywhere.

If this is even possible, I'd appreciate any advice. If not, then I appreciate the assistance none the less!

Slot Sign Up.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
5								2-Mar	3-Mar	4-Mar	5-Mar	6-Mar			2-Mar	3-Mar	4-Mar	5-Mar	6-Mar
6	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday		Times	Monday	Tuesday	Wednesday	Thursday	Friday
7	Name 1		3/2				11:00AM							11:00AM
8	Name 2						11:30AM	Name 1						11:30AM
9	Name 3						12:00PM							12:00PM
10	Name 4						12:30PM							12:30PM
11	Name 5						1:00PM							1:00PM
12	Name 6
13	Name 7						2:00PM							2:00PM
14	Name 8						2:30PM							2:30PM
15	Name 9						3:00PM							3:00PM
16	Name 10						3:30PM							3:30PM
17	Name 11						4:00PM							4:00PM
18	Name 12						4:30PM							4:30PM
19	Name 13						5:00PM							5:00PM
20							5:30PM							5:30PM
21
22								2-Mar	3-Mar	4-Mar	5-Mar	6-Mar			2-Mar	3-Mar	4-Mar	5-Mar	6-Mar
23							Times	Monday	Tuesday	Wednesday	Thursday	Friday		Times	Monday	Tuesday	Wednesday	Thursday	Friday
24							11:00AM							11:00AM
25							11:30AM							11:30AM
26							12:00PM							12:00PM
27							12:30PM							12:30PM
28
29							2:00PM							2:00PM
30							2:30PM							2:30PM
31							3:00PM							3:00PM
32							3:30PM							3:30PM
33							4:00PM							4:00PM
34							4:30PM							4:30PM
35							5:00PM							5:00PM
36							5:30PM							5:30PM
37
38								2-Mar	3-Mar	4-Mar	5-Mar	6-Mar
39							Times	Monday	Tuesday	Wednesday	Thursday	Friday
40							11:00AM
41							11:30AM
42							12:00PM
43							12:30PM
44
45							2:00PM
46							2:30PM
47							3:00PM
48							3:30PM
49							4:00PM
50							4:30PM
51							5:00PM
52							5:30PM
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(COUNTIF(I7:I20,"*Name 1*"),I5,"")

Cells with Data Validation
Cell	Allow	Criteria
I7:M11	List	=$B$7:$B$19
I13:M20	List	=$B$7:$B$19
P7:T11	List	=$B$7:$B$19
P13:T20	List	=$B$7:$B$19
P24:T27	List	=$B$7:$B$19
P29:T36	List	=$B$7:$B$19
I24:M27	List	=$B$7:$B$19
I29:M36	List	=$B$7:$B$19
I40:M43	List	=$B$7:$B$19
I45:M52	List	=$B$7:$B$19

 

[mamady]

Hi Dear,

I am not sure if I got your requirement correct, but please try the following

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1								02-03-2020	03-03-2020	04-03-2020	05-03-2020	06-03-2020
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		02-03-2020	11:30AM			11:00AM
4	Name 2						11:30AM	Name 1
5	Name 3						12:00PM
6	Name 4						12:30PM
7	Name 5						1:00PM
8	Name 6
9	Name 7						2:00PM
10	Name 8						2:30PM
11	Name 9						3:00PM
12	Name 10						3:30PM
13	Name 11						4:00PM
14	Name 12						4:30PM
15	Name 13						5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3	C3	=INDEX($H$1:$L$1,--SUMPRODUCT((A3=$H$3:$L$16)*1))
D3	D3	=INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$L$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16)))

Best Regards

 

[CallMeKris]

Hi Dear,

I am not sure if I got your requirement correct, but please try the following

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1								02-03-2020	03-03-2020	04-03-2020	05-03-2020	06-03-2020
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		02-03-2020	11:30AM			11:00AM
4	Name 2						11:30AM	Name 1
5	Name 3						12:00PM
6	Name 4						12:30PM
7	Name 5						1:00PM
8	Name 6
9	Name 7						2:00PM
10	Name 8						2:30PM
11	Name 9						3:00PM
12	Name 10						3:30PM
13	Name 11						4:00PM
14	Name 12						4:30PM
15	Name 13						5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3	C3	=INDEX($H$1:$L$1,--SUMPRODUCT((A3=$H$3:$L$16)*1))
D3	D3	=INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$L$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16)))

Best Regards

Hiya Mamady! thank you so much for helping me out! Thank you for cleaning up the design! It looks so much better! ?

Everything seems to be working beautifully on the first H column, however, once it gets into the following days, the time slot won't print and the date is stuck on the H1 value. Any ideas what might be happening?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								2-Mar-20	3-Mar-20	4-Mar-20	5-Mar-20	6-Mar-20	7-Mar-20	8-Mar-20	9-Mar-20	10-Mar-20	11-Mar-20
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		3/2/2020	#N/A			11:00AM
4	Name 2						11:30AM		Name 1
5	Name 3						12:00PM
6	Name 4						12:30PM
7	Name 5						1:00PM
8	Name 6
9	Name 7						2:00PM
10	Name 8						2:30PM
11	Name 9						3:00PM
12	Name 10						3:30PM
13	Name 11						4:00PM
14	Name 12						4:30PM
15	Name 13						5:00PM
16							5:30PM
Sheet2

Cell Formulas
Range	Formula
C3	C3	=INDEX($H$1:$Q$1,--SUMPRODUCT((A3=$H$3:$Q$16)*1))
D3	D3	=INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16)))

Cells with Data Validation
Cell	Allow	Criteria
H3:Q16	List	=$A$3:$A$15

 

[mamady]

Hi Dear,

I am glad to help.

The formula in C needs some refinement. A question: will Name 1 occur multiple times during the week? If yes, which date you would like to retrieve in the blue table?

Regards

 

[CallMeKris]

Hi Dear,

I am glad to help.

The formula in C needs some refinement. A question: will Name 1 occur multiple times during the week? If yes, which date you would like to retrieve in the blue table?

Regards

Nope! The name needs only to occur once with whatever slot data is chosen.

 

[CallMeKris]

Nope! The name needs only to occur once with whatever slot data is chosen.

I don't know if explaining what this will be used for will help but just in case. The weeks table will be expanded to cover a month and the individuals that will be listed will pick from the month a day and time that works for them to have a monthly meeting. I hope that makes sense.

Once again, I thank you very much for your time and assistance!

 

[mamady]

I don't know if explaining what this will be used for will help but just in case. The weeks table will be expanded to cover a month and the individuals that will be listed will pick from the month a day and time that works for them to have a monthly meeting. I hope that makes sense.

Once again, I thank you very much for your time and assistance!

I am pleased to be able to support

Please find the below

time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								43892	43893	43894	43895	43896	43897	43898	43899	43900	43901
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		43893	11:30AM			11:00AM
4	Name 2		43895	12:30PM			11:30AM		Name 1
5	Name 3		43898	11:00AM			12:00PM							Name 3
6	Name 4		43892	2:30PM			12:30PM				Name 2
7							1:00PM
8
9							2:00PM
10							2:30PM	Name 4
11							3:00PM
12							3:30PM
13							4:00PM
14							4:30PM
15							5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=IF(A3<>0,INDEX($H$1:$Q$1,1,SUMPRODUCT((--(A3=$H$3:$Q$3)--(A3=$H$4:$Q$4)--(A3=$H$5:$Q$5)--(A3=$H$6:$Q$6)--(A3=$H$7:$Q$7)--(A3=$H$8:$Q$8)--(A3=$H$9:$Q$9)--(A3=$H$10:$Q$10)--(A3=$H$11:$Q$11)--(A3=$H$12:$Q$12)--(A3=$H$13:$Q$13)--(A3=$H$14:$Q$14)--(A3=$H$15:$Q$15)--(A3=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D3:D15	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")

Regards

 

[CallMeKris]

I am pleased to be able to support

Please find the below

time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								43892	43893	43894	43895	43896	43897	43898	43899	43900	43901
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		43893	11:30AM			11:00AM
4	Name 2		43895	12:30PM			11:30AM		Name 1
5	Name 3		43898	11:00AM			12:00PM							Name 3
6	Name 4		43892	2:30PM			12:30PM				Name 2
7							1:00PM
8
9							2:00PM
10							2:30PM	Name 4
11							3:00PM
12							3:30PM
13							4:00PM
14							4:30PM
15							5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=IF(A3<>0,INDEX($H$1:$Q$1,1,SUMPRODUCT((--(A3=$H$3:$Q$3)--(A3=$H$4:$Q$4)--(A3=$H$5:$Q$5)--(A3=$H$6:$Q$6)--(A3=$H$7:$Q$7)--(A3=$H$8:$Q$8)--(A3=$H$9:$Q$9)--(A3=$H$10:$Q$10)--(A3=$H$11:$Q$11)--(A3=$H$12:$Q$12)--(A3=$H$13:$Q$13)--(A3=$H$14:$Q$14)--(A3=$H$15:$Q$15)--(A3=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D3:D15	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")

Regards

This works beautifully! Thank you Mamady!

I feel like a fool though. I tried to expand the ranges to add in more weeks, but then it appears the formulas don't input the data anymore once I do that. I'm certain I'm missing something and doing something incorrectly. May I ask what I need to do edit in order to add in more weeks?

I promise this will be the last question!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1								Mar-20	Mar-20	Mar-20	Mar-20	Mar-20	Jun-20	Jun-20	Jun-20	Jun-20	Jun-20	Jul-20	Jul-20	Jul-20	Jul-20	Jul-20	Aug-20	Aug-20	Aug-20	Aug-20	Aug-20	Sep-20	Sep-20	Sep-20	Sep-20	Sep-20
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		#N/A				11:00AM
4	Name 2		3/5/2020	12:30PM			11:30AM
5	Name 3		6/8/2020	12:00PM			12:00PM							Name 3
6	Name 4		3/2/2020	2:30PM			12:30PM				Name 2					Name 6								Name 1
7	Name 5		6/11/2020	2:30PM			1:00PM
8	Name 6		6/10/2020	12:30PM
9	Name 7		#SPILL!				2:00PM
10	Name 8		#SPILL!				2:30PM	Name 4									Name 5
11	Name 9		#SPILL!				3:00PM
12	Name 10		#SPILL!				3:30PM
13	Name 11		#SPILL!				4:00PM
14	Name 12		#SPILL!				4:30PM
15	Name 13		#N/A				5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3	C3	=IF(A3<>0,INDEX($H$1:$AF$1,1,SUMPRODUCT((--(A3=$H$3:$AF$3)--(A3=$H$4:$AF$4)--(A3=$H$5:$Q$5)--(A3=$H$6:$Q$6)--(A3=$H$7:$Q$7)--(A3=$H$8:$Q$8)--(A3=$H$9:$Q$9)--(A3=$H$10:$Q$10)--(A3=$H$11:$Q$11)--(A3=$H$12:$Q$12)--(A3=$H$13:$Q$13)--(A3=$H$14:$Q$14)--(A3=$H$15:$Q$15)--(A3=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D3	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$AF$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16,R3:R16,S3:S16,T3:T16,U3:U16,V3:V16,W3:W16,X3:X16,Y3:Y16,Z3:Z16,AA3:AA16,AB3:AB16,AC3:AC16,AD3:AD16,AE3:AE16,AF3:AF16))),"")
C4:C7,C9:C14	C4	=IF(A4<>0,INDEX($H$1:$Q$1,1,SUMPRODUCT((--(A4=$H$3:$Q$3)--(A4=$H$4:$Q$4)--(A4=$H$5:$Q$5)--(A4=$H$6:$Q$6)--(A4=$H$7:$Q$7)--(A4=$H$8:$Q$8)--(A4=$H$9:$Q$9)--(A4=$H$10:$Q$10)--(A4=$H$11:$Q$11)--(A4=$H$12:$Q$12)--(A4=$H$13:$Q$13)--(A4=$H$14:$Q$14)--(A4=$H$15:$Q$15)--(A4=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D4,D6,D9:D15	D4	=IFERROR(INDEX($G$3:$G$16,MATCH(A4,CHOOSE(MATCH(C4,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
D5	D5	=IFERROR(INDEX($G$3:$G$16,MATCH(A5,CHOOSE(MATCH(C5,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
D7	D7	=IFERROR(INDEX($G$3:$G$16,MATCH(A7,CHOOSE(MATCH(C7,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
C8	C8	=IF(A8<>0,INDEX($H$1:$V$1,1,SUMPRODUCT((--(A8=$H$3:$Q$3)--(A8=$H$4:$Q$4)--(A8=$H$5:$Q$5)--(A8=$H$6:$Q$6)--(A8=$H$7:$Q$7)--(A8=$H$8:$Q$8)--(A8=$H$9:$Q$9)--(A8=$H$10:$Q$10)--(A8=$H$11:$Q$11)--(A8=$H$12:$Q$12)--(A8=$H$13:$Q$13)--(A8=$H$14:$Q$14)--(A8=$H$15:$Q$15)--(A8=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D8	D8	=IFERROR(INDEX($G$3:$G$16,MATCH(A8,CHOOSE(MATCH(C8,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
C15	C15	=IF(A15<>0,INDEX($H$1:$V$1,1,SUMPRODUCT((--(A15=$H$3:$V$3)--(A15=$H$4:$V$4)--(A15=$H$5:$V$5)--(A15=$H$6:$V$6)--(A15=$H$7:$V$7)--(A15=$H$8:$V$8)--(A15=$H$9:$V$9)--(A15=$H$10:$V$10)--(A15=$H$11:$V$11)--(A15=$H$12:$V$12)--(A15=$H$13:$V$13)--(A15=$H$14:$V$14)--(A15=$H$15:$V$15)--(A15=$H$16:$V$16))*(COLUMN($A$1:$J$14)))),"")

 

[mamady]

This works beautifully! Thank you Mamady!

I feel like a fool though. I tried to expand the ranges to add in more weeks, but then it appears the formulas don't input the data anymore once I do that. I'm certain I'm missing something and doing something incorrectly. May I ask what I need to do edit in order to add in more weeks?

I promise this will be the last question!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1								Mar-20	Mar-20	Mar-20	Mar-20	Mar-20	Jun-20	Jun-20	Jun-20	Jun-20	Jun-20	Jul-20	Jul-20	Jul-20	Jul-20	Jul-20	Aug-20	Aug-20	Aug-20	Aug-20	Aug-20	Sep-20	Sep-20	Sep-20	Sep-20	Sep-20
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		#N/A				11:00AM
4	Name 2		3/5/2020	12:30PM			11:30AM
5	Name 3		6/8/2020	12:00PM			12:00PM							Name 3
6	Name 4		3/2/2020	2:30PM			12:30PM				Name 2					Name 6								Name 1
7	Name 5		6/11/2020	2:30PM			1:00PM
8	Name 6		6/10/2020	12:30PM
9	Name 7		#SPILL!				2:00PM
10	Name 8		#SPILL!				2:30PM	Name 4									Name 5
11	Name 9		#SPILL!				3:00PM
12	Name 10		#SPILL!				3:30PM
13	Name 11		#SPILL!				4:00PM
14	Name 12		#SPILL!				4:30PM
15	Name 13		#N/A				5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3	C3	=IF(A3<>0,INDEX($H$1:$AF$1,1,SUMPRODUCT((--(A3=$H$3:$AF$3)--(A3=$H$4:$AF$4)--(A3=$H$5:$Q$5)--(A3=$H$6:$Q$6)--(A3=$H$7:$Q$7)--(A3=$H$8:$Q$8)--(A3=$H$9:$Q$9)--(A3=$H$10:$Q$10)--(A3=$H$11:$Q$11)--(A3=$H$12:$Q$12)--(A3=$H$13:$Q$13)--(A3=$H$14:$Q$14)--(A3=$H$15:$Q$15)--(A3=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D3	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$AF$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16,R3:R16,S3:S16,T3:T16,U3:U16,V3:V16,W3:W16,X3:X16,Y3:Y16,Z3:Z16,AA3:AA16,AB3:AB16,AC3:AC16,AD3:AD16,AE3:AE16,AF3:AF16))),"")
C4:C7,C9:C14	C4	=IF(A4<>0,INDEX($H$1:$Q$1,1,SUMPRODUCT((--(A4=$H$3:$Q$3)--(A4=$H$4:$Q$4)--(A4=$H$5:$Q$5)--(A4=$H$6:$Q$6)--(A4=$H$7:$Q$7)--(A4=$H$8:$Q$8)--(A4=$H$9:$Q$9)--(A4=$H$10:$Q$10)--(A4=$H$11:$Q$11)--(A4=$H$12:$Q$12)--(A4=$H$13:$Q$13)--(A4=$H$14:$Q$14)--(A4=$H$15:$Q$15)--(A4=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D4,D6,D9:D15	D4	=IFERROR(INDEX($G$3:$G$16,MATCH(A4,CHOOSE(MATCH(C4,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
D5	D5	=IFERROR(INDEX($G$3:$G$16,MATCH(A5,CHOOSE(MATCH(C5,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
D7	D7	=IFERROR(INDEX($G$3:$G$16,MATCH(A7,CHOOSE(MATCH(C7,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
C8	C8	=IF(A8<>0,INDEX($H$1:$V$1,1,SUMPRODUCT((--(A8=$H$3:$Q$3)--(A8=$H$4:$Q$4)--(A8=$H$5:$Q$5)--(A8=$H$6:$Q$6)--(A8=$H$7:$Q$7)--(A8=$H$8:$Q$8)--(A8=$H$9:$Q$9)--(A8=$H$10:$Q$10)--(A8=$H$11:$Q$11)--(A8=$H$12:$Q$12)--(A8=$H$13:$Q$13)--(A8=$H$14:$Q$14)--(A8=$H$15:$Q$15)--(A8=$H$16:$Q$16))*(COLUMN($A$1:$J$14)))),"")
D8	D8	=IFERROR(INDEX($G$3:$G$16,MATCH(A8,CHOOSE(MATCH(C8,$H$1:$Q$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,M3:M16,N3:N16,O3:O16,P3:P16,Q3:Q16))),"")
C15	C15	=IF(A15<>0,INDEX($H$1:$V$1,1,SUMPRODUCT((--(A15=$H$3:$V$3)--(A15=$H$4:$V$4)--(A15=$H$5:$V$5)--(A15=$H$6:$V$6)--(A15=$H$7:$V$7)--(A15=$H$8:$V$8)--(A15=$H$9:$V$9)--(A15=$H$10:$V$10)--(A15=$H$11:$V$11)--(A15=$H$12:$V$12)--(A15=$H$13:$V$13)--(A15=$H$14:$V$14)--(A15=$H$15:$V$15)--(A15=$H$16:$V$16))*(COLUMN($A$1:$J$14)))),"")

Hi Dear,

I have a few observations to point out:

1- You did not expand all the time ranges to accommodate the newly added dates. I'll show you visually

mistake:

2- the newly added ranges the formula under the column D are not fixed with $ sign, thus will move if the formula is dragged down. You can fixate the reference by manually typing $ or pressing F4 button after selecting the range.

mistake:
... $AE$3:$AE$16,AF3:AF16...

3- Moreover, another tip. Whenever you expand the number of days/date, please adjust the last part of the formula under column C to reflect the total number of days. In your case, after adding those new dates/days, J should be changed to Y (J = 14 columns whereas Y = 25 columns):

mistake:
*(COLUMN($A$1:$J$14)))),"")


Anyhow, please find the below-updated formulas:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1								43892	43893	43894	43895	43896	43989	43990	43991	43992	43993	44019	44020	44021	44022	44023	44050	44051	44052	44053	44054	44081	44082	44083	44084	44085
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		44051	12:30PM			11:00AM
4	Name 2		43895	12:30PM			11:30AM		Name 12												Name 7											Name 9
5	Name 3		43990	12:00PM			12:00PM							Name 3
6	Name 4		43892	2:30PM			12:30PM				Name 2					Name 6			Name 11					Name 1							Name 8
7	Name 5		43993	2:30PM			1:00PM
8	Name 6		43992	12:30PM
9	Name 7		44022	11:30AM			2:00PM
10	Name 8		44084	12:30PM			2:30PM	Name 4				Name 13					Name 5											Name 10
11	Name 9		44085	11:30AM			3:00PM
12	Name 10		44081	2:30PM			3:30PM
13	Name 11		44020	12:30PM			4:00PM
14	Name 12		43893	11:30AM			4:30PM
15	Name 13		43896	2:30PM			5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=IF(A3<>0,INDEX($H$1:$AF$1,1,SUMPRODUCT((--(A3=$H$3:$AF$3)--(A3=$H$4:$AF$4)--(A3=$H$5:$AF$5)--(A3=$H$6:$AF$6)--(A3=$H$7:$AF$7)--(A3=$H$8:$AF$8)--(A3=$H$9:$AF$9)--(A3=$H$10:$AF$10)--(A3=$H$11:$AF$11)--(A3=$H$12:$AF$12)--(A3=$H$13:$AF$13)--(A3=$H$14:$AF$14)--(A3=$H$15:$AF$15)--(A3=$H$16:$AF$16))*COLUMN(A1:Y14))),"")
D3:D15	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$AF$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,$M$3:$M$16,$N$3:$N$16,$O$3:$O$16,$P$3:$P$16,$Q$3:$Q$16,$R$3:$R$16,$S$3:$S$16,$T$3:$T$16,$U$3:$U$16,$V$3:$V$16,$W$3:$W$16,$X$3:$X$16,$Y$3:$Y$16,$Z$3:$Z$16,$AA$3:$AA$16,$AB$3:$AB$16,$AC$3:$AC$16,$AD$3:$AD$16,$AE$3:$AE$16,$AF$3:$AF$16))),"")

Regards

 

[CallMeKris]

Hi Dear,

I have a few observations to point out:

1- You did not expand all the time ranges to accommodate the newly added dates. I'll show you visually

mistake:
View attachment 31450

2- the newly added ranges the formula under the column D are not fixed with $ sign, thus will move if the formula is dragged down. You can fixate the reference by manually typing $ or pressing F4 button after selecting the range.

mistake:
... $AE$3:$AE$16,AF3:AF16...

3- Moreover, another tip. Whenever you expand the number of days/date, please adjust the last part of the formula under column C to reflect the total number of days. In your case, after adding those new dates/days, J should be changed to Y (J = 14 columns whereas Y = 25 columns):

mistake:
*(COLUMN($A$1:$J$14)))),"")


Anyhow, please find the below-updated formulas:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1								43892	43893	43894	43895	43896	43989	43990	43991	43992	43993	44019	44020	44021	44022	44023	44050	44051	44052	44053	44054	44081	44082	44083	44084	44085
2	TLS	Task	Date	Time Slot	Completed		Times	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
3	Name 1		44051	12:30PM			11:00AM
4	Name 2		43895	12:30PM			11:30AM		Name 12												Name 7											Name 9
5	Name 3		43990	12:00PM			12:00PM							Name 3
6	Name 4		43892	2:30PM			12:30PM				Name 2					Name 6			Name 11					Name 1							Name 8
7	Name 5		43993	2:30PM			1:00PM
8	Name 6		43992	12:30PM
9	Name 7		44022	11:30AM			2:00PM
10	Name 8		44084	12:30PM			2:30PM	Name 4				Name 13					Name 5											Name 10
11	Name 9		44085	11:30AM			3:00PM
12	Name 10		44081	2:30PM			3:30PM
13	Name 11		44020	12:30PM			4:00PM
14	Name 12		43893	11:30AM			4:30PM
15	Name 13		43896	2:30PM			5:00PM
16							5:30PM
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=IF(A3<>0,INDEX($H$1:$AF$1,1,SUMPRODUCT((--(A3=$H$3:$AF$3)--(A3=$H$4:$AF$4)--(A3=$H$5:$AF$5)--(A3=$H$6:$AF$6)--(A3=$H$7:$AF$7)--(A3=$H$8:$AF$8)--(A3=$H$9:$AF$9)--(A3=$H$10:$AF$10)--(A3=$H$11:$AF$11)--(A3=$H$12:$AF$12)--(A3=$H$13:$AF$13)--(A3=$H$14:$AF$14)--(A3=$H$15:$AF$15)--(A3=$H$16:$AF$16))*COLUMN(A1:Y14))),"")
D3:D15	D3	=IFERROR(INDEX($G$3:$G$16,MATCH(A3,CHOOSE(MATCH(C3,$H$1:$AF$1),$H$3:$H$16,$I$3:$I$16,$J$3:$J$16,$K$3:$K$16,$L$3:$L$16,$M$3:$M$16,$N$3:$N$16,$O$3:$O$16,$P$3:$P$16,$Q$3:$Q$16,$R$3:$R$16,$S$3:$S$16,$T$3:$T$16,$U$3:$U$16,$V$3:$V$16,$W$3:$W$16,$X$3:$X$16,$Y$3:$Y$16,$Z$3:$Z$16,$AA$3:$AA$16,$AB$3:$AB$16,$AC$3:$AC$16,$AD$3:$AD$16,$AE$3:$AE$16,$AF$3:$AF$16))),"")

Regards

I'm smarter now than I was before! Thank you for everything Mamady! You've been a real blessing!