Hi, Afarag!
A formula only solution is available as long as you change your coloring system from green to red by placing a 1 instead of a blank, i.e., until now you selected a cell and changed its color to red, from now on you'll type a 1 in that cell. A conditional formatting condition handles "hiding" the number typed changing it font to the same background color (red).
Give a look at the uploaded file. It uses many dynamic named ranges for easy referencing:
UserCell: =Data!$C$6
IDCell: =Data!$C$7
AgentCell: =Data!$C$12
DataTable: =DESREF(Sceduale!$A$4;;;CONTARA(Sceduale!$A:$A)-1;CONTARA(Sceduale!$3:$3)+3) -----> in english: =OFFSET(Sceduale!$A$4,,,COUNTA(Sceduale!$A:$A)-1,COUNTA(Sceduale!$3:$3)+3)
(take care when adding titles to actual empty cells in row 3 or column A; you should adjust the previous references of -1 and +3)
IDList: =DESREF(DataTable;;0;;1) -----> in english: =OFFSET(DataTable,,0,,1)
UserList: =DESREF(DataTable;;1;;1) -----> in english: =OFFSET(DataTable,,1,,1)
AgentList: =DESREF(DataTable;;2;;1) -----> in english: =OFFSET(DataTable,,2,,1)
ScheduleTable: =DESREF(DataTable;;5;;COLUMNAS(DataTable)-5) -----> in english: =OFFSET(DataTable,,5,,COLUMNS(DataTable)-5)
TimeList: =DESREF(ScheduleTable;;-1;;1
-----> in english: =OFFSET(ScheduleTable,,-1,,1
IDIndex: =SI.ERROR(COINCIDIR(AgentCell;AgentList;0);0) -----> in english: =IFERROR(MATCH(AgentCell,AgentList,0),0)
(for referencing the related row of the selected agent)
IDTable: =DESREF(ScheduleTable;IDIndex-1;;1
-----> in english: =OFFSET(ScheduleTable,IDIndex-1,,1,)
Formulas:
Data!C12: =SI.ERROR(INDICE(AgentList;SI.ERROR(COINCIDIR(IDCell;IDList;0);COINCIDIR(UserCell;UserList;0)));"") -----> in english: =IFERROR(INDEX(AgentList,IFERROR(MATCH(IDCell,IDList,0),MATCH(UserCell,UserList,0))),"")
Data!D11/F11/H11/J11: ="Meeting "&ENTERO((COLUMNA()-2)/2) -----> in english: ="Meeting "&INT((COLUMN()-2)/2)
(changed merged cells to center on the selection)
Data!D13:K13 : =SI(D14<>0;INDICE(TimeList;1;D15);"") -----> in english: =IF(D14<>0,INDEX(TimeList,1,D15),"")
Data!D14/F14/H14/J14: =SI.ERROR(COINCIDIR(1;DESREF(IDTable;;C15;;COLUMNAS(IDTable)-C15);0);0) -----> in english: =IFERROR(MATCH(1,OFFSET(IDTable,,C15,,COLUMNS(IDTable)-C15),0),0)
Data!E14/G14/I14/K14: =SI(D14<>0;SI.ERROR(COINCIDIR(VERDADERO;DESREF(IDTable;;D15;;COLUMNAS(IDTable)-D15)="";0);0);0) -----> in english: =IF(D14<>0,IFERROR(MATCH(TRUE,OFFSET(IDTable,,D15,,COLUMNS(IDTable)-D15)="",0),0),0)
(array formula, remember that array formulas should be entered with
Ctrl-Shift-Enter instead of just
Enter.)
Data!D15/F15/H15/J15: =SI(D14<>0;C15+D14;0) -----> in english: =IF(D14<>0,C15+D14,0)
Data!E15/G15/I15/K15: =SI(D14<>0;D15+E14-1;0) -----> in english:
=IF(D14<>0,D15+E14-1,0)
Block cells of D11:E15 should be copied to the right to add more meetings.
Conditional formatting:
Sceduale!F4:BM129 (i.e., ScheduleTable dynamic named range): =F4=1 -----> font red, background red
Just advise if any issue.
Regards!
PS: Please be as kind as to replace
by "; )", unquoted and without space. Maybe r1c1 one day changes it as he asked for other cases than :D and as he was told for ; ) and : P too, at least of what I know as I did.
