Another Employee Vacation Tracking Calendar Question

tsroque

Board Regular
Joined
Jan 19, 2007
Messages
126
Office Version
  1. 365
Creating a calendar to track vacation and sick time. Would like to use "V" for vacation and "S" for sick.

TOTALS tab:
The first row contains my headers with my data starting in row 2:
A1 = employee no
B1 = employee name
C1 = days awarded
D1 = days used
E1 = days left

It's then followed by 12 tabs...one for each month in the year: Jan, Feb, Mar, etc...

MONTHLY tabs...
On each monthly tab, the employee's number and names are listed downward starting with A2 being the header...

A2 = employee no
B2 = employee name

My dates run across from C2:AG2 in the following format: 01|02|03, etc... We would then mark a V or an S under the date and across an employees row to track his/her requests.

I need a formula on the TOTALS tab starting in D2 for my first employee to count how many "V"'s in all 12 tabs for the employee number listed in A2.

I can do a simple Countif, but I need it to look at and match the employee number from the Totals tab to all the monthly tabs and count the number of "V"s without the employee having to be on the same row, etc... The employees listed on Totals may not necessarily match the employees listed each month, and the Totals tab only lists the current employees, so being able to COUNT based on if the employee number matches that row, is important. I was thinking down the lines of a VLOOKUP, COUNTIF combo? But since I'm dealing with multiple tabs, I'm not sure how to pursue this.

Any sugestions for a formula to use? Your suggestions are HIGHLY appreciated and always a tremendous help!!!!

Could you also please give me a formula to use in Totals!D2 if I was calcualting based on hours instead of V and S? Instead of Days Awarded, it would be Hours Awarded etc... And I would use 8 instead of V for a full day's vacation request. Right now, this new manager is thinking that no one will request 1/2 a day, etc... so I would like to present both options to him.

thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
First, on each monthly spreadsheet, I would have a column that totals the Vacation and Sick days for each employee. So, for example, cell AH3 would have the formula:

=COUNTIF($C3:$AG3, "V")

and cell AI3 would have the formula

=COUNTIF($C3:$AG3, "S")

Then, on your totals spreadsheet, you would reference column AH for the vacation days and AI for the sick days. If you wanted to pull the Vacation and Sick days for each month and display them separately, your formula on the main spreadsheet would be something like this:

[in cell F3, Vacation days for January]
=IF(ISNA(MATCH($A3, Jan!$A$3:$A$100, 0)), 0, INDEX(Jan!$AH$3:$AH$100, MATCH($A3, Jan!$A$3:$A$100, 0)))

[in cell G3, Sick days for January]
=IF(ISNA(MATCH($A3, Jan!$A$3:$A$100, 0)), 0, INDEX(Jan!$AI$3:$AI$100, MATCH($A3, Jan!$A$3:$A$100, 0)))

For other months, replace all instances of "Jan" with the appropriate monthly tab name. You can then add up these individual cells to form an annual total.

If you don't want to show the individual months' Vacation and Sick days, but just want to show the total, your formulas are going to be really long. They would be something like:

=IF(ISNA(MATCH($A3, Jan!$A$3:$A$100, 0)), 0, INDEX(Jan!$AH$3:$AH$100, MATCH($A3, Jan!$A$3:$A$100, 0)))+IF(ISNA(MATCH($A3, Feb!$A$3:$A$100, 0)), 0, INDEX(Feb!$AH$3:$AH$100, MATCH($A3, Feb!$A$3:$A$100, 0)))+...+IF(ISNA(MATCH($A3, Dec!$A$3:$A$100, 0)), 0, INDEX(Dec!$AH$3:$AH$100, MATCH($A3, Dec!$A$3:$A$100, 0)))
 
Upvote 0
Hi MisterCrash!

I took your advice and went for the shorter formulas :D. It's working out great! You've definitely bumped up my appreciation level at work.

I have another question... Is there a way to inform people as they're inserting vacation time if an employee has used up all their time? Basically, I want to set up an alert. If Empl01 used up all 10 days, as the admin is trying to type in the 11th day for that employee that a message would pop up informing them that the employee has no more time available, and will not allow them to insert anymore "V"s unless more time is added to that employee. Would I set it up as a condition or a validation? And how could I do that working under method you gave me above?

Here's my info:
Totals tab
*Employee Number is in cell A4 (validation rule set up for no duplicates)
*Days Left for that employee is in E4

Jan tab
*Employee Number is in A3 (validation rule set up for no duplicates)
*Total Vacation for the month is in C3
*My calendar range where I enter "V" and "S" is E3:AI3, down...

Thanks again!
 
Upvote 0
Well, I have a way to do it, but it's kind of involved. Here are the main steps:

1. On the monthly tab for January, include a reference to the total amount of remaining days the employee has, which is contained on the Totals page. I put it in column AJ. Here's my formula for cell AJ3:

=IF(ISNA(MATCH($A3, Total!$A$3:$A$100, 0)), 99, INDEX(Total!$E$3:$E$100, MATCH($A3, Total!$A$3:$A$100, 0)))

Of course, if you have more employees employees than that, increase the last row in this formula. This formula will be the same for all tabs. If the employee is not on the Total tab, this formula will evaluate to 99. Since only active employees show up on the Total tab, this basically makes it so that we don't worry about the vacation schedules of people who have already left the company, since they can't take any more vacation once they've left.

2. Next to that, in column AK, have a formula that shows whether or not the employee is over the vacation limit. Here's what I put in cell AK3:

=IF(AJ3<0, "Yes", "No")

3. Somewhere in the workbook, I have two lists made: SickVacList and SickList. The SickVacList is composed of two elements: the letters S and V. The SickList only has one element: the letter S. Basically, to make the SickVacList, put the letters S and V in adjacent cells somewhere in the spreadsheet, highlight them, then do Insert | Name | Define, and call it "SickVacList" (without the quotes). Then, just highlight the S by itself and define that name as "SickList"

4. Now you're ready for some data validation. On your monthly spreadsheets, highlight E3:AI100, then go to Data | Validation. On the Settings tab, in the Allow blank, choose "List", and in the Source blank, put this:

=IF($AK3="Yes", SickList, SickVacList)

I would uncheck the in-cell dropdown checkbox in this case, but you could leave it active if you'd like. On the Error tab of the Data Validation dialog box, make sure that the checkbox at the top is checked, and the Style is set to "Stop". In the Error message blank, put in something like this:

"This employee has already taken the maximum vacation allowed."

5. Repeat this for all the other monthly tabs.
 
Upvote 0
Now that's pretty cool! Thanks for all your help and the great tips on layout! I was even able to add more bells and whistles with the formulas you gave me.

Everything was extremely helpful, and a great lesson learned!
 
Upvote 0
One more question... If I want to convert this spreadsheet to sum the actual hours used instead of counting V, H, or S, what formula would I replace the validation formula with once total hours have been used, and a user tries to enter more than awarded?
 
Upvote 0
If you're converting to hours, then you'll want to change the validation from List to Whole Number, with a minimum value of 0, and a maximum value of:

=IF($AK3="Yes", 0, 8)

So, if the employee has already taken his full allotment of vacation, you can't have anything higher than zero in the cell. If not, you can put in anything up to 8 hours for the day, or the maximum vacation that he has remaining.

One issue with this is that you can only have one warning message for data validation, which means you'll get the same message if you try to put in a number higher than 8 in a cell as you will when you exceed the maximum number of vacation days. You'll have to adjust the error message to cover both possibilities (e.g., "You have either put in over 8 hours, or you have exceeded the maximum vacation allowed.").
 
Upvote 0
Thanks again! LOL! I almost had it, but my eyes are killing me from staring at this thing all weekend.

So far, everyone is loving all the bells and whistles, so again, thanks for your help! :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top