(for referencing the related row of the selected agent) IDIndex: =SI.ERROR(COINCIDIR(AgentCell AgentList 0) 0) -> in english: =IFERROR(MATCH(AgentCell,AgentList,0),0) TimeList: =DESREF(ScheduleTable -1 1 -> in english: =OFFSET(ScheduleTable,-1,1 ScheduleTable: =DESREF(DataTable 5 COLUMNAS(DataTable)-5) -> in english: =OFFSET(DataTable,5,COLUMNS(DataTable)-5) UserList: =DESREF(DataTable 1 1) -> in english: =OFFSET(DataTable,1,1)ĪgentList: =DESREF(DataTable 2 1) -> in english: =OFFSET(DataTable,2,1) IDList: =DESREF(DataTable 0 1) -> in english: =OFFSET(DataTable,0,1) (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) It uses many dynamic named ranges for easy referencing:ĭataTable: =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)
A conditional formatting condition handles "hiding" the number typed changing it font to the same background color (red). 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.
0 kommentar(er)
