If specific text within multiple ranges, input corresponding cell values

CallMeKris

New Member
Joined
Feb 3, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRST
52-Mar3-Mar4-Mar5-Mar6-Mar2-Mar3-Mar4-Mar5-Mar6-Mar
6TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayTimesMondayTuesdayWednesdayThursdayFriday
7Name 13/211:00AM11:00AM
8Name 211:30AMName 111:30AM
9Name 312:00PM12:00PM
10Name 412:30PM12:30PM
11Name 51:00PM1:00PM
12Name 6
13Name 72:00PM2:00PM
14Name 82:30PM2:30PM
15Name 93:00PM3:00PM
16Name 103:30PM3:30PM
17Name 114:00PM4:00PM
18Name 124:30PM4:30PM
19Name 135:00PM5:00PM
205:30PM5:30PM
21
222-Mar3-Mar4-Mar5-Mar6-Mar2-Mar3-Mar4-Mar5-Mar6-Mar
23TimesMondayTuesdayWednesdayThursdayFridayTimesMondayTuesdayWednesdayThursdayFriday
2411:00AM11:00AM
2511:30AM11:30AM
2612:00PM12:00PM
2712:30PM12:30PM
28
292:00PM2:00PM
302:30PM2:30PM
313:00PM3:00PM
323:30PM3:30PM
334:00PM4:00PM
344:30PM4:30PM
355:00PM5:00PM
365:30PM5:30PM
37
382-Mar3-Mar4-Mar5-Mar6-Mar
39TimesMondayTuesdayWednesdayThursdayFriday
4011:00AM
4111:30AM
4212:00PM
4312:30PM
44
452:00PM
462:30PM
473:00PM
483:30PM
494:00PM
504:30PM
515:00PM
525:30PM
Sheet1
Cell Formulas
RangeFormula
D7D7=IF(COUNTIF(I7:I20,"*Name 1*"),I5,"")
Cells with Data Validation
CellAllowCriteria
I7:M11List=$B$7:$B$19
I13:M20List=$B$7:$B$19
P7:T11List=$B$7:$B$19
P13:T20List=$B$7:$B$19
P24:T27List=$B$7:$B$19
P29:T36List=$B$7:$B$19
I24:M27List=$B$7:$B$19
I29:M36List=$B$7:$B$19
I40:M43List=$B$7:$B$19
I45:M52List=$B$7:$B$19
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Dear,

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

Book2
ABCDEFGHIJKL
102-03-202003-03-202004-03-202005-03-202006-03-2020
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFriday
3Name 102-03-202011:30AM11:00AM
4Name 211:30AMName 1
5Name 312:00PM
6Name 412:30PM
7Name 51:00PM
8Name 6
9Name 72:00PM
10Name 82:30PM
11Name 93:00PM
12Name 103:30PM
13Name 114:00PM
14Name 124:30PM
15Name 135:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3C3=INDEX($H$1:$L$1,--SUMPRODUCT((A3=$H$3:$L$16)*1))
D3D3=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
 
Upvote 0
Hi Dear,

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

Book2
ABCDEFGHIJKL
102-03-202003-03-202004-03-202005-03-202006-03-2020
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFriday
3Name 102-03-202011:30AM11:00AM
4Name 211:30AMName 1
5Name 312:00PM
6Name 412:30PM
7Name 51:00PM
8Name 6
9Name 72:00PM
10Name 82:30PM
11Name 93:00PM
12Name 103:30PM
13Name 114:00PM
14Name 124:30PM
15Name 135:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3C3=INDEX($H$1:$L$1,--SUMPRODUCT((A3=$H$3:$L$16)*1))
D3D3=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
ABCDEFGHIJKLMNOPQ
12-Mar-203-Mar-204-Mar-205-Mar-206-Mar-207-Mar-208-Mar-209-Mar-2010-Mar-2011-Mar-20
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 13/2/2020#N/A11:00AM
4Name 211:30AMName 1
5Name 312:00PM
6Name 412:30PM
7Name 51:00PM
8Name 6
9Name 72:00PM
10Name 82:30PM
11Name 93:00PM
12Name 103:30PM
13Name 114:00PM
14Name 124:30PM
15Name 135:00PM
165:30PM
Sheet2
Cell Formulas
RangeFormula
C3C3=INDEX($H$1:$Q$1,--SUMPRODUCT((A3=$H$3:$Q$16)*1))
D3D3=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
CellAllowCriteria
H3:Q16List=$A$3:$A$15
 
Upvote 0
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
 
Upvote 0
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. :giggle:
 
Upvote 0
Nope! The name needs only to occur once with whatever slot data is chosen. :giggle:
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!
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
143892438934389443895438964389743898438994390043901
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 14389311:30AM11:00AM
4Name 24389512:30PM11:30AMName 1
5Name 34389811:00AM12:00PMName 3
6Name 4438922:30PM12:30PMName 2
7  1:00PM
8  
9  2:00PM
10  2:30PMName 4
11  3:00PM
12  3:30PM
13  4:00PM
14  4:30PM
15  5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3:C15C3=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:D15D3=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
 
Upvote 0
I am pleased to be able to support

Please find the below

time.xlsx
ABCDEFGHIJKLMNOPQ
143892438934389443895438964389743898438994390043901
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 14389311:30AM11:00AM
4Name 24389512:30PM11:30AMName 1
5Name 34389811:00AM12:00PMName 3
6Name 4438922:30PM12:30PMName 2
7  1:00PM
8  
9  2:00PM
10  2:30PMName 4
11  3:00PM
12  3:30PM
13  4:00PM
14  4:30PM
15  5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3:C15C3=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:D15D3=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. :confused: 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Mar-20Mar-20Mar-20Mar-20Mar-20Jun-20Jun-20Jun-20Jun-20Jun-20Jul-20Jul-20Jul-20Jul-20Jul-20Aug-20Aug-20Aug-20Aug-20Aug-20Sep-20Sep-20Sep-20Sep-20Sep-20
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 1#N/A 11:00AM
4Name 23/5/202012:30PM11:30AM
5Name 36/8/202012:00PM12:00PMName 3
6Name 43/2/20202:30PM12:30PMName 2Name 6Name 1
7Name 56/11/20202:30PM1:00PM
8Name 66/10/202012:30PM
9Name 7#SPILL! 2:00PM
10Name 8#SPILL! 2:30PMName 4Name 5
11Name 9#SPILL! 3:00PM
12Name 10#SPILL! 3:30PM
13Name 11#SPILL! 4:00PM
14Name 12#SPILL! 4:30PM
15Name 13#N/A 5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3C3=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)))),"")
D3D3=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:C14C4=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:D15D4=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))),"")
D5D5=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))),"")
D7D7=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))),"")
C8C8=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)))),"")
D8D8=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))),"")
C15C15=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)))),"")
 
Upvote 0
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. :confused: 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Mar-20Mar-20Mar-20Mar-20Mar-20Jun-20Jun-20Jun-20Jun-20Jun-20Jul-20Jul-20Jul-20Jul-20Jul-20Aug-20Aug-20Aug-20Aug-20Aug-20Sep-20Sep-20Sep-20Sep-20Sep-20
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 1#N/A 11:00AM
4Name 23/5/202012:30PM11:30AM
5Name 36/8/202012:00PM12:00PMName 3
6Name 43/2/20202:30PM12:30PMName 2Name 6Name 1
7Name 56/11/20202:30PM1:00PM
8Name 66/10/202012:30PM
9Name 7#SPILL! 2:00PM
10Name 8#SPILL! 2:30PMName 4Name 5
11Name 9#SPILL! 3:00PM
12Name 10#SPILL! 3:30PM
13Name 11#SPILL! 4:00PM
14Name 12#SPILL! 4:30PM
15Name 13#N/A 5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3C3=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)))),"")
D3D3=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:C14C4=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:D15D4=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))),"")
D5D5=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))),"")
D7D7=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))),"")
C8C8=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)))),"")
D8D8=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))),"")
C15C15=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:
1612701843560.png


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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
143892438934389443895438964398943990439914399243993440194402044021440224402344050440514405244053440544408144082440834408444085
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 14405112:30PM11:00AM
4Name 24389512:30PM11:30AMName 12Name 7Name 9
5Name 34399012:00PM12:00PMName 3
6Name 4438922:30PM12:30PMName 2Name 6Name 11Name 1Name 8
7Name 5439932:30PM1:00PM
8Name 64399212:30PM
9Name 74402211:30AM2:00PM
10Name 84408412:30PM2:30PMName 4Name 13Name 5Name 10
11Name 94408511:30AM3:00PM
12Name 10440812:30PM3:30PM
13Name 114402012:30PM4:00PM
14Name 124389311:30AM4:30PM
15Name 13438962:30PM5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3:C15C3=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:D15D3=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
 

Attachments

  • 1612703637677.png
    1612703637677.png
    1.3 KB · Views: 3
Upvote 0
Solution
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
143892438934389443895438964398943990439914399243993440194402044021440224402344050440514405244053440544408144082440834408444085
2TLSTaskDateTime SlotCompletedTimesMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFridayMondayTuesdayWednesdayThursdayFriday
3Name 14405112:30PM11:00AM
4Name 24389512:30PM11:30AMName 12Name 7Name 9
5Name 34399012:00PM12:00PMName 3
6Name 4438922:30PM12:30PMName 2Name 6Name 11Name 1Name 8
7Name 5439932:30PM1:00PM
8Name 64399212:30PM
9Name 74402211:30AM2:00PM
10Name 84408412:30PM2:30PMName 4Name 13Name 5Name 10
11Name 94408511:30AM3:00PM
12Name 10440812:30PM3:30PM
13Name 114402012:30PM4:00PM
14Name 124389311:30AM4:30PM
15Name 13438962:30PM5:00PM
165:30PM
Sheet1
Cell Formulas
RangeFormula
C3:C15C3=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:D15D3=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!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top