Ned I took your advice and blew up the entire workbook and redesigned it. To get around my previous problem I made drop down menus linked to drop down menus and it works great, the first menu has all the companies listed the second menu only lists the employees with each company.
I am having some problems with calculating something and my formula just isn't working like I would expect it to. Basically what I am trying to do is check 6 cells for a Yes each yes= 8 hours but there is a cap of 40 hours for the week then I am subtracting the working hours from that number(we are tracking idle time). I nested a bunch of if statements and I cant seem to figure out what I am doing wrong. It always returns a Not on Site value, is it a simple syntax error or should I approach this differently maybe with a count if? I even tried reversing my arguments from highest to lowest to the opposite and its the same thing
Here's what I have currently:
=IF(IF(IF(IF(IF(IF(IF(IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)>=40,40-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=32),32-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=24),24-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=16),16-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=8),8-F15),IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=0),0,"Not on Site")
I am having some problems with calculating something and my formula just isn't working like I would expect it to. Basically what I am trying to do is check 6 cells for a Yes each yes= 8 hours but there is a cap of 40 hours for the week then I am subtracting the working hours from that number(we are tracking idle time). I nested a bunch of if statements and I cant seem to figure out what I am doing wrong. It always returns a Not on Site value, is it a simple syntax error or should I approach this differently maybe with a count if? I even tried reversing my arguments from highest to lowest to the opposite and its the same thing
Here's what I have currently:
=IF(IF(IF(IF(IF(IF(IF(IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)>=40,40-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=32),32-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=24),24-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=16),16-F15,IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=8),8-F15),IF(B122="Y",8,0)+IF(C122="Y",8,0)+IF(D122="Y",8,0)+IF(E122="Y",8,0)+IF(F122="Y",8,0)+IF(G122="Y",8,0)=0),0,"Not on Site")