• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Employee Overtime Tracker

sn152

sn152

Member
Hi All,

Good Day!

Iam in the process of creating a an employee overtime tracker. I have attached a sample file. In the sheet "OT Tracker", I can calculate the OT hours of each employee on a daily basis. But what I need is, on the sheet "Associate wise - Summary" when i enter the Emp ID, it should automatically pull the employee name and all the details of the employee like OT hours for all the dates, Office transport, OT reason. But the problem is I dont know where to input the details like Office transport, OT reason(date wise for each employee). On the whole Iam totally confused with this.

It would be great if someone could help me with this.

Thanks in advance.
 

Attachments

  • OT Tracker - Sample.xlsx
    86.3 KB · Views: 43
sn152 said:
Hi All,


Thanks in advance.
Click to expand...
Hi

Where do u get the data from? Manually input?
However, look into modified sheet. If that is usable, just copy the vlookup as u like, or make a dummycopy of the finished result. Then I can do the formulas for you if you like.

PS: The Id was formatted as text, undo it in your own master sheet. (right click on green label in a cell)
 

Attachments

  • OT Tracker - Sample first test.xlsx
    89.8 KB · Views: 21
First of all thank you so much for showing interest in my project.
Hi Somendra Misra,

Yes there is no OT reason and transport details in the sheet that I have shared. I am little bit confused as where to add those details. Because I have to add it for each user for each day. If you can suggest me an idea I will add it.

Hi Kyrre,

On a daily basis i have to manually input the data. But on the Associate wise summary sheet, when I enter the emp id I would like to get the details of that employee.

Thanks!
 
@sn152 I can fix the summary sheet, I need to have a dummysheet to figure out where to get data from.
Add dummy data to first line first worker in main tab. You could then copy summarysheet to another tab and fill inn dummydata. Then I could fix the formula for you for the entire sheet.
 
sn152 said:
when I enter the emp id I would like to get the details of that employee.

Thanks!
Click to expand...
You`ll do that now, in sheet posted by me. It is not finished because I need some more details. But, manually input from 450 workers:confused:? I think you will need another system for this.
 
Hi Kyrre,

I have attached a new file. Anyway we have to manually enter the data for each associate. So can we create a user form to enter the data one by one. Because around 5 people will be accessing this file. So there is a chance where the formats or some data might be changed. Can you please help to create a user form this?

Thanks!
 

Attachments

  • Feb 2014 - Sample.xlsx
    22.3 KB · Views: 14
Hi Kyrre, Thank you.
This is fine. We have to enter the data in the sheet "Input". And when we click on transfer it will automatically transfer the data to the next sheet. Please let me know whether my understanding is correct? If so, we should add other fields also in the sheet "Input". Because we have it only till "start time (24 hrs format)".
 
sn152 said:
Hi Kyrre, Thank you.
This is fine. We have to enter the data in the sheet "Input". And whe.
Click to expand...

Yes, it was for testing. Now it works. Just copy old data from your sheet, paste in this new sheet and start using transferbutton for new data. Change datavalidation an other as u like in inputboxes.
 

Attachments

  • Working macro.xlsm
    61.7 KB · Views: 34
This is awesome. It's working fine. Thaks a ton for this :). Is ther a way where I can prevent the users from editing the sheet 2. Because if i protect the sheet 2 and input the data in the sheet 1 and when I click transfer button, will it transfers to sheet 2?

Once again thank you so much for helping me... :)
 
Great! actually this macro could be written a lot shorter, but it works fine for me too.And it is easy to rewrite and understand. I have not tested sheet protect and macro. Does protect work for u?
 
If I protect the sheet 2 it is not working. Is there a way to prevent anyone from manually editing the sheet 2?
 
Here's a different macro that's a little shorter and works with protecting/unprotecting the sheet. I gave you the option of assigning a password to the protection. Copy it into the VBE module, and then right-click the Transfer button, assign macro, select the TransferData macro.
Code: 
Sub TransferData()
'Do you have a password for sheet2?
'If not, change this to ""
Const myPass = "1234"

'Setup the variables we will use
Dim lastRow As Long
Dim blankCells As Range
Dim InputCells As Range

'What cells are we going to copy?
Set InputCells = Ark1.Range("E5:U5")

'check if we have blank cells
On Error Resume Next
Set blankCells = InputCells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

'Stop macro if there are blanks
If Not blankCells Is Nothing Then
    MsgBox "Please fill in all of the cells.", vbOKOnly, "Blank cells"
    Exit Sub
End If

'Work with the second sheet
With Ark2
    .Unprotect myPass
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    InputCells.Copy .Cells(lastRow + 1, "A")
    .Protect myPass
End With

'Clear previous data
InputCells.ClearContents
[e5].Select
End Sub
 
Hi Kyrre and Luke thank you so much for helping me. Iam almost about to complete. Now I have added vlookup to get somedata from 3rd sheet to first sheet. But when I click transfer it transfers to sheet2 but the cells that have vlookup formula is not getting pasted. Please Help me....
 

Attachments

  • OT Tracker - HIX IB.xlsm
    56.4 KB · Views: 12
I'm afraid I don't know how to reconstruct the formulas that were on Data_Input, but since you have formulas, we want to do a paste values when we transfer, and then only clear out the cells that are constants. Change the macro to this:
Code: 
Sub Transfer()
'Do you have a password for sheet2?
'If not, change this to ""
Const myPass = "1234"

'Setup the variables we will use
Dim lastRow As Long
Dim blankCells As Range
Dim InputCells As Range

'What cells are we going to copy?
Set InputCells = Sheet1.Range("E5:U5")

'check if we have blank cells
On Error Resume Next
Set blankCells = InputCells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

'Stop macro if there are blanks
If Not blankCells Is Nothing Then
    MsgBox "Please fill in all the cells", vbOKOnly, "Blank cells"
    Exit Sub
End If

Application.ScreenUpdating = False
'Work with the second sheet
With Sheet2
    .Unprotect myPass
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    InputCells.Copy
    .Cells(lastRow + 1, "A").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    .Protect myPass
End With

'Clear previous data
InputCells.SpecialCells(xlCellTypeConstants).ClearContents
Application.ScreenUpdating = True

'Where do we want the cursor to be now?
[e5].Select
End Sub
Now, you can redo your formulas and they won't get all messed up when you hit the Transfer button.
 
Hi Luke & Kyrre,

Now the macro is wrkng fine. I have another question. I have highlighted 2 cells in the attached sheet in yellow. These cells should have only the dates. Can we restrict the users from entering any text in these 2 cells? Or can we have a calender in these 2 cells, so that the user can click on the calender and select the date?

Please help me on this....
 

Attachments

  • OT_Tracker_-_HIX_IB(1).xlsm
    56.5 KB · Views: 41
Select the cells, then go to Data - Date Validation. Select "Date" from the dropdown. I'd also recommend giving a date range such as 1/1/2000 to 1/1/2100, just so someone can't input the number 5 and XL think you mean Jan 5, 1900. :oops:
 
Hi Luke,

I tried this and it is working. But it allows the users to copy and paste in the date cells that contains data validation.
 
You must log in or register to reply here.
Back
Top