Excel error compile procedure to large

B

bestdad

New Member
Joined
Sep 20, 2011
Messages
25
Ok i am trying to create a excel form that does a schedule. this form has a time in and timout that i want to auto calculate based off of the other data.

the person will add a 1 to each hr the employee will work. If it start at 8 than the time in is auto filled and in this example I want the time out to state 12 pm.

the way i was trying to write the code to do this made it to large and I am not even half way done for each employee in the list. we have about 25 employees.

This form will be used in large work book and the person using this form like looking at this type of format I am trying to get some of this data to auto fill for him.
example of my code per employee:
Code: 
' Calculating the Time in emploee ID 1001
If Range("E9") = "" And Range("F9") = "" And Range("G9") = "" And Range("H9") = "" And Range("I9") = "" And Range("J9") = "" And Range("K9") = "" And Range("L9") = "" And Range("M9") = "" And Range("N9") = "" And Range("O9") = "" And Range("P9") = "" And Range("Q9") = "" And Range("R9") = "" And Range("S9") = "" Then
Range("C9") = ("Off")
End If
If Range("E9") = 1 Then
Range("c9") = ("8:00")
End If
If Range("F9") = 1 And Range("E9") = "" Then
Range("c9") = ("9:00")
End If
If Range("G9") = 1 And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("10:00")
End If
If Range("H9") = 1 And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("11:00")
End If
If Range("I9") = 1 And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("12:00")
End If
If Range("J9") = 1 And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("1:00 pm")
End If
If Range("K9") = 1 And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("2:00 pm")
End If
If Range("L9") = 1 And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("3:00 pm")
End If
If Range("M9") = 1 And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("4:00 pm")
End If
If Range("N9") = 1 And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("5:00 pm")
End If
If Range("O9") = 1 And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("6:00 pm")
End If
If Range("P9") = 1 And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("7:00 pm")
End If
If Range("Q9") = 1 And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("8:00 pm")
End If
If Range("R9") = 1 And Range("Q9") = "" And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("9:00 pm")
End If
If Range("S9") = 1 And Range("R9") = "" And Range("Q9") = "" And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("10:00 pm")
End If
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
It seems like you could do this with just formulas if I understand you correctly.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;;">Employee</td><td style="font-weight: bold;text-align: center;;">In</td><td style="font-weight: bold;text-align: center;;">Out</td><td style="font-weight: bold;text-align: center;;">8:00 AM</td><td style="font-weight: bold;text-align: center;;">9:00 AM</td><td style="font-weight: bold;text-align: center;;">10:00 AM</td><td style="font-weight: bold;text-align: center;;">11:00 AM</td><td style="font-weight: bold;text-align: center;;">12:00 PM</td><td style="font-weight: bold;text-align: center;;">1:00 PM</td><td style="font-weight: bold;text-align: center;;">2:00 PM</td><td style="font-weight: bold;text-align: center;;">3:00 PM</td><td style="font-weight: bold;text-align: center;;">4:00 PM</td><td style="font-weight: bold;text-align: center;;">5:00 PM</td><td style="font-weight: bold;text-align: center;;">6:00 PM</td><td style="font-weight: bold;text-align: center;;">7:00 PM</td><td style="font-weight: bold;text-align: center;;">8:00 PM</td><td style="font-weight: bold;text-align: center;;">9:00 PM</td><td style="font-weight: bold;text-align: center;;">10:00 PM</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Jim</td><td style="text-align: center;;">8:00 AM</td><td style="text-align: center;;">2:00 PM</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Sue</td><td style="text-align: center;;">12:00 PM</td><td style="text-align: center;;">7:00 PM</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Bob</td><td style="text-align: center;;">4:00 PM</td><td style="text-align: center;;">10:00 PM</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E9:S9,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">1,E9:S9,0</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E9:S9,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">2,E9:S9,1</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C10</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E10:S10,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">1,E10:S10,0</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E10:S10,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">2,E10:S10,1</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E11:S11,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">1,E11:S11,0</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E11:S11,1</font>),INDEX(<font color="Red">$E$8:$S$8,MATCH(<font color="Green">2,E11:S11,1</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Hello,

I have tried to simplify this in VBA. I think there would be a better way to set this up.

Maybe this example will help you:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TI_TO()<br><SPAN style="color:#007F00">' Calculating the Time in emploee ID 1001</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> strTime     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>            <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> EmptyRange:<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("E9:S9").Find(1, LookIn:=xlValues).Address<br>            <SPAN style="color:#00007F">Case</SPAN> "$E$9"<br>                strTime = "8:00"<br>            <SPAN style="color:#00007F">Case</SPAN> "$F$9"<br>                strTime = "9:00"<br>            <SPAN style="color:#00007F">Case</SPAN> "$G$9"<br>                strTime = "10:00"<br>            <SPAN style="color:#00007F">Case</SPAN> "$H$9"<br>                strTime = "11:00"<br>            <SPAN style="color:#00007F">Case</SPAN> "$I$9"<br>                strTime = "12:00"<br>            <SPAN style="color:#00007F">Case</SPAN> "$J$9"<br>                strTime = "1:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$K$9"<br>                strTime = "2:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$L$9"<br>                strTime = "3:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$M$9"<br>                strTime = "4:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$N$9"<br>                strTime = "5:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$O$9"<br>                strTime = "6:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$P$9"<br>                strTime = "7:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$Q$9"<br>                strTime = "8:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$R$9"<br>                strTime = "9:00 pm"<br>            <SPAN style="color:#00007F">Case</SPAN> "$S$9"<br>                strTime = "10:00 pm"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            Range("C9").Value = strTime<br>    <SPAN style="color:#00007F">GoTo</SPAN> RangeComplete:<br>EmptyRange:<br>    Range("C9").Value = "Off"<br>RangeComplete:<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
bestdad said:
Ok i am trying to create a excel form that does a schedule. this form has a time in and timout that i want to auto calculate based off of the other data.

the person will add a 1 to each hr the employee will work. If it start at 8 than the time in is auto filled and in this example I want the time out to state 12 pm.

the way i was trying to write the code to do this made it to large and I am not even half way done for each employee in the list. we have about 25 employees.

This form will be used in large work book and the person using this form like looking at this type of format I am trying to get some of this data to auto fill for him.
example of my code per employee:
Code: 
' Calculating the Time in emploee ID 1001
If Range("E9") = "" And Range("F9") = "" And Range("G9") = "" And Range("H9") = "" And Range("I9") = "" And Range("J9") = "" And Range("K9") = "" And Range("L9") = "" And Range("M9") = "" And Range("N9") = "" And Range("O9") = "" And Range("P9") = "" And Range("Q9") = "" And Range("R9") = "" And Range("S9") = "" Then
Range("C9") = ("Off")
End If
If Range("E9") = 1 Then
Range("c9") = ("8:00")
End If
If Range("F9") = 1 And Range("E9") = "" Then
Range("c9") = ("9:00")
End If
If Range("G9") = 1 And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("10:00")
End If
If Range("H9") = 1 And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("11:00")
End If
If Range("I9") = 1 And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("12:00")
End If
If Range("J9") = 1 And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("1:00 pm")
End If
If Range("K9") = 1 And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("2:00 pm")
End If
If Range("L9") = 1 And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("3:00 pm")
End If
If Range("M9") = 1 And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("4:00 pm")
End If
If Range("N9") = 1 And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("5:00 pm")
End If
If Range("O9") = 1 And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("6:00 pm")
End If
If Range("P9") = 1 And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("7:00 pm")
End If
If Range("Q9") = 1 And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("8:00 pm")
End If
If Range("R9") = 1 And Range("Q9") = "" And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("9:00 pm")
End If
If Range("S9") = 1 And Range("R9") = "" And Range("Q9") = "" And Range("P9") = "" And Range("O9") = "" And Range("N9") = "" And Range("M9") = "" And Range("L9") = "" And Range("K9") = "" And Range("J9") = "" And Range("I9") = "" And Range("H9") = "" And Range("G9") = "" And Range("F9") = "" And Range("E9") = "" Then
Range("c9") = ("10:00 pm")
End If
Click to expand...

You could also divide your large procedure until several smaller ones and then call them in order

ie

Call Proc1
Call Proc2
ect ect
 
Upvote 0
Thank you ahead of time. I will be looking at each reply. Just by a quick glance some look really promising.
 
Upvote 0
AlphaFrog said:
It seems like you could do this with just formulas if I understand you correctly.


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Employee</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">In</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Out</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">8:00 AM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">9:00 AM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">10:00 AM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">11:00 AM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">12:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">3:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">4:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">5:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">7:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">8:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">9:00 PM</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">10:00 PM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">Jim</TD><TD style="TEXT-ALIGN: center">8:00 AM</TD><TD style="TEXT-ALIGN: center">2:00 PM</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">Sue</TD><TD style="TEXT-ALIGN: center">12:00 PM</TD><TD style="TEXT-ALIGN: center">7:00 PM</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">Bob</TD><TD style="TEXT-ALIGN: center">4:00 PM</TD><TD style="TEXT-ALIGN: center">10:00 PM</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C9</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E9:S9,1),INDEX($E$8:$S$8,MATCH(1,E9:S9,0)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D9</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E9:S9,1),INDEX($E$8:$S$8,MATCH(2,E9:S9,1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C10</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E10:S10,1),INDEX($E$8:$S$8,MATCH(1,E10:S10,0)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D10</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E10:S10,1),INDEX($E$8:$S$8,MATCH(2,E10:S10,1)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C11</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E11:S11,1),INDEX($E$8:$S$8,MATCH(1,E11:S11,0)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D11</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF(E11:S11,1),INDEX($E$8:$S$8,MATCH(2,E11:S11,1)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...
Thank you that worked great. Also I want to say thanks to everyone else that suggested idea.s
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel chart with a line that will adjust certain cells
Replies
0
Views
301
jondavis1987
J
LearningByDoing
  • Solved
No correct calculation with Nested IF + AND Function
Replies
4
Views
120
Alex Blakenburg
A
J
  • Question Question
total hours worked calculation with breaks
Replies
1
Views
574
awoohaw
awoohaw
X
  • Solved
Color matching date through VBA
Replies
5
Views
582
xsmurf
X
U
  • Solved
SumIf formula with multiple criteria from different tab
Replies
3
Views
497
Alex Blakenburg
A

Forum statistics

Threads
1,224,503
Messages
6,179,135
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top