Cell U5 using the following code to pull all agents if dates in range 'EMPLOYEE LIST'!$V$2:$BG$1000 is = to DATE($J$1,$G$1,$H$1 and dragged down to Cell U26
'EMPLOYEE LIST'!$V$2:$BG$1000 will be manually format date to be "RED" when it is a PM Hours.
'EMPLOYEE LIST'!$G$2:$G$1000 = Names of employee
What im need the VBA to do is conditional format employee name U5:U26 to be "RED" based on if the date corresponding to the Name is RED.
ex.
sheet Employee List
in BASE SHEET if DATE($J$1,$G$1,$H$1)=
J1= 2020 (year)
G1= 1 (month)
H1= 19 (Day)
U5:U26 results should be
any help is greatly appreciated. let me know if you have any question.
Thanks
VBA Code:
=IFERROR(INDEX('EMPLOYEE LIST'!$G$2:$G$1000,AGGREGATE(15,6,(ROW('EMPLOYEE LIST'!$G$2:$G$1000)-ROW('EMPLOYEE LIST'!G$2)+1)/(('EMPLOYEE LIST'!$V$2:$BG$1000=DATE($J$1,$G$1,$H$1))),ROWS($U$5:U5))),"")
'EMPLOYEE LIST'!$V$2:$BG$1000 will be manually format date to be "RED" when it is a PM Hours.
'EMPLOYEE LIST'!$G$2:$G$1000 = Names of employee
What im need the VBA to do is conditional format employee name U5:U26 to be "RED" based on if the date corresponding to the Name is RED.
ex.
sheet Employee List
JOHN | 1/18 | 1/19 | ||
MIKE | 1/19 | |||
KIM | 1/19 | |||
JENN | 1/19 | 1/20 | ||
JAMES | 1/1 | 1/15 | 1/19 |
in BASE SHEET if DATE($J$1,$G$1,$H$1)=
J1= 2020 (year)
G1= 1 (month)
H1= 19 (Day)
U5:U26 results should be
JOHN |
MIKE |
KIM |
JENN |
JAMES |
any help is greatly appreciated. let me know if you have any question.
Thanks