tsroque
Board Regular
- Joined
- Jan 19, 2007
- Messages
- 127
- Office Version
- 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!
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!