Production counts

[Oneblondeboy]

I'm trying to track the parts we run each hour on our line I used the =now() to get the current time. I would like to put each hour on my sheet then put the number we ran, I have one cell that keeps track of the total count so it will always change but I want my count for that hour to stay in the cell next to it when the times match, I have tried =if but it never seems to work. I dont have a vast knowledge of excel so please try to keep it as simple as you can.

 

[Smitty]

Welcome to the Board!

Can you post an example of what you've got now? See the link beneath my sig for how.

I'd probably set up a grid of production hours & days:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date/Time</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">1/4/2011</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">9:00 AM</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10:00 AM</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">11:00 AM</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1:00 PM</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">3:00 PM</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

HTH,

 

[Oneblondeboy]

I tried to post my sheet but its not working so well sorry, but I'll try explaining using your table. In cell A1 I would use the =now() and format it to show only time. Now I pull my data from the robot and refresh it every minute so Ill put that in B1 now when the current time is 9:00 am like you have I would like the value in B1 to go in B2 then at ten am I would like that value in B3, so the value in B1 will always be changing but I would like to know what that number is at certain times, let me know if I am clear enough.

Thanks

 

[Smitty]

How are you refreshing the data from the robot?

Are those values sequential? I.E. The robot starts a run at 0 pieces, then completes 1,2,3, etc., and those sequential numbers are coming in? Or is it giving you a total number up to that point? I.E. 0, 12, 27, 43, etc.

 

[Oneblondeboy]

Sorry work was busy, I pull the info using Data tab the robots are on the network with an IP add so its like importing off the web, the numbers do go in order but since the shortest time excel refreshes is every minute I would miss a part or two but thats ok for what I'm doing,

 

[Smitty]

OK, this may do what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> LenB(Range("B1").Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            LR = Cells(Rows.Count, "B").End(xlUp).Offset(1).Row<br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                Cells(LR, "B").Value = Range("B1").Value<br>                Cells(LR, "A").Value = Now<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

When the worksheet calculates it's going to put B1's value in the next blank row in column B, then put a time stamp in column A. I don't really know how you would address the hours part though, since the data won't necessarily come in a those times.