Background: I am a new intern at a custom interior design company and I have been hired to streamline and simplify the process that they go through with tracking inventory and the time spent on different jobs so they can calculate their job costs accurately. I have spent days working on this project and coming up with different ideas on how to go about creating this system. I want to try and stay within Excel to keep this project as cost effective as possible. I'm still not entirely sure if what i want to accomplish is even possible but I thought I might as well bring my problem here before calling it quits.
__________________________________________________________________________________________________________________________________________
I want to implement a bar-code system= There will be a bar-code for all inventory, there will also be a specific bar for each Person in the shop, Job ID, a Build Process ID, and Cost Code ID.
Now what I want to happen is have every person scan into the job they are working on, so they would scan their own Name barcode, then they would scan Job ID, Process ID, and Cost Code ID and these will display across a row in Excel with a price stamp of them Clocking IN to the project. Then while in the project we want them to be able to scan the inventory used for each project and this will also be displayed in the excel sheet. Then when the person scans their Name barcode again it will give a TIME OUT stamp with date and time in it. Each time a person clocks out, whether they clock into the same job or a different job we want this to start in a new row. Also each different person clocking in will start on a new row.
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]JobID[/TD]
[TD]ProcessID[/TD]
[TD]PayID[/TD]
[TD]DateTime IN[/TD]
[TD]Date+Time OUT[/TD]
[TD]TotalTIME Worked[/TD]
[TD]Inventory1[/TD]
[TD]Inventory2[/TD]
[TD]Inventory3[/TD]
[TD]Inventory(x)[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]13-052-A[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]6/23/14 8:35 am[/TD]
[TD]6/23/14 10:30 am[/TD]
[TD]2[/TD]
[TD]Plywood[/TD]
[TD]Sandpaper[/TD]
[TD]Screws[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]14-523-A[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]6/23/14 9:00 am[/TD]
[TD]6/23/14 10:30 am[/TD]
[TD]1.5[/TD]
[TD]Primer[/TD]
[TD]Paint[/TD]
[TD]Brush[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]15-552-B[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]6/23/14 9:30 am[/TD]
[TD]6/23/14 1:30 pm[/TD]
[TD]4[/TD]
[TD]Sandpaper[/TD]
[TD]Screws[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]13-052-A[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]6/23/14 11:00 am[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD]10
2x4 16'[/TD]
[TD]5
2x6 16'[/TD]
[TD]screws[/TD]
[TD]Nails[/TD]
[/TR]
</tbody>[/TABLE]
(This is a sample of what i would like to see)
We have 12 employees doing this simultaneously on many different jobs and if we could keep track of all of them in a single excel sheet that would be ideal. Employees also are constantly clocking in and out of jobs throughout the day. I dont know if it would be better to have a Name barcode for each person or to assign each person their own barcode scanner (If so will we be able to track which employee is scanning into each respective job).
Thank you for all your help and I hope that this is possible to do.
__________________________________________________________________________________________________________________________________________
I want to implement a bar-code system= There will be a bar-code for all inventory, there will also be a specific bar for each Person in the shop, Job ID, a Build Process ID, and Cost Code ID.
Now what I want to happen is have every person scan into the job they are working on, so they would scan their own Name barcode, then they would scan Job ID, Process ID, and Cost Code ID and these will display across a row in Excel with a price stamp of them Clocking IN to the project. Then while in the project we want them to be able to scan the inventory used for each project and this will also be displayed in the excel sheet. Then when the person scans their Name barcode again it will give a TIME OUT stamp with date and time in it. Each time a person clocks out, whether they clock into the same job or a different job we want this to start in a new row. Also each different person clocking in will start on a new row.
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]JobID[/TD]
[TD]ProcessID[/TD]
[TD]PayID[/TD]
[TD]DateTime IN[/TD]
[TD]Date+Time OUT[/TD]
[TD]TotalTIME Worked[/TD]
[TD]Inventory1[/TD]
[TD]Inventory2[/TD]
[TD]Inventory3[/TD]
[TD]Inventory(x)[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]13-052-A[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]6/23/14 8:35 am[/TD]
[TD]6/23/14 10:30 am[/TD]
[TD]2[/TD]
[TD]Plywood[/TD]
[TD]Sandpaper[/TD]
[TD]Screws[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]14-523-A[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]6/23/14 9:00 am[/TD]
[TD]6/23/14 10:30 am[/TD]
[TD]1.5[/TD]
[TD]Primer[/TD]
[TD]Paint[/TD]
[TD]Brush[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]15-552-B[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]6/23/14 9:30 am[/TD]
[TD]6/23/14 1:30 pm[/TD]
[TD]4[/TD]
[TD]Sandpaper[/TD]
[TD]Screws[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]13-052-A[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]6/23/14 11:00 am[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD]10
2x4 16'[/TD]
[TD]5
2x6 16'[/TD]
[TD]screws[/TD]
[TD]Nails[/TD]
[/TR]
</tbody>[/TABLE]
(This is a sample of what i would like to see)
We have 12 employees doing this simultaneously on many different jobs and if we could keep track of all of them in a single excel sheet that would be ideal. Employees also are constantly clocking in and out of jobs throughout the day. I dont know if it would be better to have a Name barcode for each person or to assign each person their own barcode scanner (If so will we be able to track which employee is scanning into each respective job).
Thank you for all your help and I hope that this is possible to do.
