Help creating a userform and then merging the form data to a worksheet?

[firstrock]

Hello all!

I have just finished an attendance tracking spreadsheet for the agency that I work for. The basic sheet layout is as follows:

• Setting- Allows for changing the year, user defined holidays and auto populating US holidays into the calendars on the months sheets based on year throughout the spreadsheet
• Attendance Calendar-Displays specific employee and attendance data via a drop down list by employee ID #
• Reports- Various reports on employee strength by department
• Employee Data- ID #, Name, Title, Department, Hiring Date, Mil or Civ
• 12 Months- sheet for each month of the year, employee data is auto populated into each month via the IF command

I have tweaked this spreadsheet the way I want it and am happy with the way it works. What I am wondering is if I can create a USERFORM that is separate from this spreadsheet so that it can be emailed to each department head. The department head will fill out the employee via a drop down list on the form, then input a specific attendance code such as P or S. The date will either be input manually or auto populated. This daily completed form will then be emailed back to the person tracking attendance company wide and that person will merge the attendance USERFORM data into the correct month sheet and date.

I am trying to make data entry a minimum for the person tracking the attendance with the spreadsheet. The person tracking the attendance currently has plans to just email the spreadsheet around and have the department heads fill in the attendance data. While this will work, it is slow, has privacy concerns and is susceptible to data integrity issues. This is why I want a USERFORM so that the data can be reviewed before being merged into the spreadsheet. Is this possible?

Thanks for taking the time to read/respond!

[royUK]

You can't emil only a userform. You could have a workbook to collect the data that you distribute then import the returned data into the master worksheet. This distributed workbook could have a userform in it.

[firstrock]

royUK

That was what I was planning on doing. Sorry I wasn't clearer on that.

Any suggestions how I go about creating this userform?

Thanks

[firstrock]

OK, so I have created the user form. What I need to do now is to merge the user form data into the attendance spreadsheet. I need a formula(?) that will merge/insert the user form data into the appropriate worksheet based on the system date. So, if the end user fills the form for: John Doe, 6-17-2012, P, then I need to merge this data into the June worksheet and then the appropriate cell that corresponds to the date from the user form.

Anyone have any idea how to go about this?





Attendance User Form.xlsm MEPS_Attendance_Calendar.xlsm

[royUK]

Your form needs to save the entry into a sheet in the same workbook. You then import the input to the calendar workbook

[firstrock]

RoyUK

I don't want to simply just import the form data, I need to have the form data input into the worksheet based on the date. When the user imports the data, I want the data to go into a specific cell automatically. This would change daily due to the date being different.

I'm trying to do this so that the user does not have to manually input each employee's daily attendance daily. That would be time consuming.

I hope I have explained what I am trying to do.