Match + copy + Absence tally + coloring (yikes!)

[shmoes]

Hello,

I am trying to complete the following action, and am not sure how to begin.

I have 1 Work book and 2 sheets.

On sheet 1 I have a list of employees and thier employee number
there is also a monthly grid for the year beside the list of names
----------------------------------------
-Example
Employee id | Name | Jan | Feb | March | Etc..
12345 John Doe
----------------------------------------

On sheet 2 I have what I am calling a running list of absenteeism.

-----------------------------------------
-Example
Employee id | Name |Date Sick | Date Returned | Total days | Visit
12345 John Doe 03-01-2013 03-06-2013 5 Yes
-----------------------------------------

I would like the following actions to take place
- When entering the employee id on page 2 I would like it to match to the employee id on page 1 and copy the corresponsing name on that line over to page 2.
- When an absence date is entered I would like the total days copied to sheet 1 in the appropriate month.
- If there is more then one absences entry for an employee in the same month I would like it to add the total days
- If there is a visit in the entry I would like it to highlight or color the field in the month of the visit.

I hope that makes sense! Any help is really appreciated, If I am looking for too much.. I understand.

Thanks!



~AZ

 

[SkipVought]

hi,

The METHOD that you specify will be very difficult to achieve.

I'd restructure your workbook as follows:
[tt]
sheet1 - Master Employee Table - (empID, Name etc...)
sheet2 - Absentee Table - (empID, AbsDate, AbsHours, AbsReason)
sheet3 - Absentee report using either PivotTable or MS Query
[/tt]
I'd use Named Ranges faq68-1331
I'd use Data > Validation --LIST dropdown to populate the Absentee Table.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

Part of the reason why this isn't eay is that there are 4 different conditions that will affect the dates:
1. Both Start Date (SD) and End Date (ED) are within the same month
2. SD begins before the month, and ED is in the month
3. SD begins before the month and ED is in another month
4. SD in the month and ED is in another month.

For the above conditions, the formula for calcuating days would be the following (for the month of January):
1. =ED - SD
2. =ED - 1/1/13
3. 2/1/13 - 1/1/13 - 1
4. 2/1/13 - SD -1

Note: I would make the column headings in the Sheet 1 actually be dates, but using a Special Number format of mmmm to display the names of the months, so in the formula above you would use the column headings instead of 1/1/13 & 2/1/13.

 

[shmoes]

Thanks for the responses guys, I will review the posted link and see what I come up with.

With regards to the SD ED problem, for my purposes only the month of the initial day of absence counts.
Otherwise I only need the end date to give me a total number of days absent or it wouldn't matter.

~AZ