Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
I have been racking my brains out, I have employees with senority that the
vacation is based on years employed. If the employee has 1-3 years in, he gets 1 week vacation, If the employee has 4-8 years in, he gets 2 weeks vacation. If the employee has 9-15 years in, he gets 3 weeks vacation. If the employee has 16-20 years in, he gets 4 weeks vacation If the employee has 21-25 years in, he gets 5 weeks vacation How can I make excel pick up the number of years worked and just print the number of weeks vacation the employee would receive? I know it can be done, I did it before, but I lost the spreadsheet and I just can't remember how I did it. Old age is sneeking up on me!! Thanks Steve -- Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
All you need to do is set up a vlookup table. The first column of the table will be the bottom threshold of each seniority grouping and the second column will be the weeks of vacation. for each employee, you would just enter a vlookup like this: =vlookup(A1, D1:E5,2) where A1 = years of service and D1:E5 is the table described above. Since you don't tell the vlookup to find an exact match, it will select the value that is just less than A1 if there isn't an exact match. Let us know if you have more questions, Chad -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488964 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
I don't know what I am doing wrong, I keep getting error messages when I
attempt to make the table as per your instruction. Belong is a sample of the page I am trying to create, I did take out some of the important information such as wages and last names etc. When I refresh my data, (everything is imported in from another program, into excel) everything updates, I would like to have the weeks of vacation to also automatically update, this is the only column that I am beating my head in over! A B C D EMPLOYEE DATE STARTED NUMBER OF YEARS NUMBER OF WEEK VACATION FLORENCIO 05/03/97 8 3 MANUEL 06/12/02 3 1 EDUARDO 04/04/05 0 0 PABLO 03/17/03 2 1 JUAN 09/26/00 5 2 FORTUNATO 08/02/96 9 JERRY 10/14/84 21 MARTIN 05/02/05 0 MARIO 05/24/04 1 JIMY 02/27/84 21 ROBERT 08/13/84 21 FRANK 06/21/04 1 ALAN 08/01/94 11 -- Thanks again for the reply, I do very much appreciate you taking the time out of your day to help out. Sincerely, Steve "cvolkert" wrote: All you need to do is set up a vlookup table. The first column of the table will be the bottom threshold of each seniority grouping and the second column will be the weeks of vacation. for each employee, you would just enter a vlookup like this: =vlookup(A1, D1:E5,2) where A1 = years of service and D1:E5 is the table described above. Since you don't tell the vlookup to find an exact match, it will select the value that is just less than A1 if there isn't an exact match. Let us know if you have more questions, Chad -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488964 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
I've built an example file based on the information provided and attached it hereto. Let me know if you have any other problems or questions. Thanks, Chad +-------------------------------------------------------------------+ |Filename: Vacation.zip | |Download: http://www.excelforum.com/attachment.php?postid=4084 | +-------------------------------------------------------------------+ -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488964 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
I think this is going to work!! Thank you so much, with all the employees,
and with the weeks changing every year, hopefully this will make printing out the senority list and vacation list for the union and for the employees faster and easier. Thanks Again. Steve -- Steve "cvolkert" wrote: I've built an example file based on the information provided and attached it hereto. Let me know if you have any other problems or questions. Thanks, Chad +-------------------------------------------------------------------+ |Filename: Vacation.zip | |Download: http://www.excelforum.com/attachment.php?postid=4084 | +-------------------------------------------------------------------+ -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488964 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Should be easy but.......
Hi - I know this is an old post but may be what I am looking for and I am
unable to capture the attachment. Can you help me get a copy of the zip file so I can see if this helps me with my problem? -- Regards - Mariela "cvolkert" wrote: I've built an example file based on the information provided and attached it hereto. Let me know if you have any other problems or questions. Thanks, Chad +-------------------------------------------------------------------+ |Filename: Vacation.zip | |Download: http://www.excelforum.com/attachment.php?postid=4084 | +-------------------------------------------------------------------+ -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488964 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stack multiple columns into one column... is there an easy way? | Excel Discussion (Misc queries) | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel | |||
Easy formula Question | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |