Linking multiple data Validations

[shantraj.antin@gmail.com]

Hi All,

From the attached workbook I am looking fr the hourly production done my employees/ respective Team.

I have raw data for employee name and job End time in column B and C.

currently I am getting the production result in column J and K based on the End Time alone.

I am trying to link the employees and the team they belong to my end result of production done by them.

From Cell E2 and F2 there is a validation option to select "Team" "Employee" based on the selected team or Employee I am looking for the result in column J and K.

For Example If we select he Team "X" the validation should show only x employees or if "All" is selected all employee irrespective of team should be available in "Select Employee" and production result to be dynamic bases on the team and employee selection.

IF both the side ALL option is selected the result should be based on the all the employees from column B

Thanks in advance for your help.

 

[Deepak]

Hope, You are looking for dependent drop down!!

http://www.contextures.com/xlDataVal02.html

 

[shantraj.antin@gmail.com]

Hi Deepak,

I will try to link drop drown. But I need your assistance when if comes to modifying the formula in column j and k.

Thank you

 

[shantraj.antin@gmail.com]

Hi Deepak,

I applied the dependent drop down.

But I am struck with the SUMPRODUCT function applied @ column J and K.

I am looking for the result in Column J and K based on the selection made from E2 and F2.

Note: IF E2= All, or IF E2= any value from drop down and F2= "All" then result should be of that particular team or irrespective of team if E2=All.

I have attached the file for your reference.

Thanks.

 

[Deepak]

Check for J & confirm the same.

=IF(F2="ALL",COUNTIFS($C$2:$C$999,">="&$G$2+H2,C$2:$C$999,"<"&$G$2+I2),COUNTIFS($B$2:$B$999,$F$2,$C$2:$C$999,">="&$G$2+H2,C$2:$C$999,"<"&$G$2+I2))

 

[shantraj.antin@gmail.com]

Hi Deepak

Thank you its working for J

 

[Deepak]

Hi,

For the time being use this in col K

=IF($F$2="All",SUMPRODUCT((MOD($C$2:$C$999,1)>=H2)*(MOD($C$2:$C$999,1)<+I2)),SUMPRODUCT((MOD($C$2:$C$999,1)>=H2)*(MOD($C$2:$C$999,1)<+I2)*($B$2:$B$999=$F$2)))

I will check & post another method later if found so far.

 

[shantraj.antin@gmail.com]

Sure and thank you Deepak