Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUP formula help!
I created a formula to reference a drop-down menu & then based on the
value...get value from the selected Lookup Table (column #2). Here's the formula: =IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12 Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10 Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven, 2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified 9-Month",VLOOKUP(L3,Nine, 2)))) What I ultimately need this formula to do is to search the chosen lookup table column based on the entered date and count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved. I am trying to get an accurate count of days an employee will work - holidays. Any help appreciated~ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUP formula help!
Hi,
Please show us some sample data in the lookup table and then explain, with respect to that sample, what you mean by "count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved" -- If this helps, please click the Yes button Cheers, Shane Devenshire "Denise" wrote: I created a formula to reference a drop-down menu & then based on the value...get value from the selected Lookup Table (column #2). Here's the formula: =IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12 Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10 Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven, 2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified 9-Month",VLOOKUP(L3,Nine, 2)))) What I ultimately need this formula to do is to search the chosen lookup table column based on the entered date and count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved. I am trying to get an accurate count of days an employee will work - holidays. Any help appreciated~ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUP formula help!
If you aren't locked into the table and just want to calculated the number of
workdays you might want to read up on the NETWORKDAYS function. You will have to have the Analysis ToolPak add-in added. Mike "Denise" wrote: I created a formula to reference a drop-down menu & then based on the value...get value from the selected Lookup Table (column #2). Here's the formula: =IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12 Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10 Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven, 2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified 9-Month",VLOOKUP(L3,Nine, 2)))) What I ultimately need this formula to do is to search the chosen lookup table column based on the entered date and count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved. I am trying to get an accurate count of days an employee will work - holidays. Any help appreciated~ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUP formula help!
Thanks-Okay here it is:
Below is a 2-column sampling of the 11-month-employee table (I have a 9,10,11,12, Haskell & NonCert employee table). What I am needing this formula to do is; If the employee's start date is 25-Feb-09 going forward calculate (minus holidays/breaks) the # of days the employee will work. I also need another cell to record (with a different formula of course) the number of days from that same start date backwards (minus holidays/breaks) the number of days that person will not work in that range of days. Each of the 6-lookup tables has a different range of days, holidays and breaks. 13-Feb-09 90 16-Feb-09 Holiday 17-Feb-09 89 18-Feb-09 88 19-Feb-09 87 20-Feb-09 86 23-Feb-09 85 24-Feb-09 84 25-Feb-09 83 26-Feb-09 82 27-Feb-09 81 02-Mar-09 Holiday 03-Mar-09 80 04-Mar-09 79 05-Mar-09 78 06-Mar-09 77 09-Mar-09 76 10-Mar-09 75 11-Mar-09 74 12-Mar-09 73 13-Mar-09 72 16-Mar-09 71 17-Mar-09 70 18-Mar-09 69 19-Mar-09 68 20-Mar-09 67 23-Mar-09 Break 24-Mar-09 Break 25-Mar-09 Break 26-Mar-09 Break 27-Mar-09 Break 30-Mar-09 66 31-Mar-09 65 01-Apr-09 64 02-Apr-09 63 03-Apr-09 62 06-Apr-09 61 07-Apr-09 60 08-Apr-09 59 09-Apr-09 58 10-Apr-09 Holiday 13-Apr-09 57 14-Apr-09 56 "Don Guillett" wrote: I would have to see a lot more info and/or a sample wb but maybe I can help your basic formula with this idea =VLOOKUP(L3,if(l2="Certified 10 Month",Ten,if(l2= "All 12 Month",StartDate)),2) You could even use CHOOSE or LOOKUP instead of the IF -- Don Guillett Microsoft MVP Excel SalesAid Software "Denise" wrote in message ... I created a formula to reference a drop-down menu & then based on the value...get value from the selected Lookup Table (column #2). Here's the formula: =IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12 Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10 Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven, 2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified 9-Month",VLOOKUP(L3,Nine, 2)))) What I ultimately need this formula to do is to search the chosen lookup table column based on the entered date and count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved. I am trying to get an accurate count of days an employee will work - holidays. Any help appreciated~ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookUP formula help!
Here is my little calculator:
Select Range: Certified 10 Month<<<This is a drop down box to choose the type of employee I am calculating for. Enter Start Date: 8/7/2008 Days Worked: 200 Minus Missed Days: Total Work Days Possible(-holidays/breaks) ALL 12 Month = 245 Certified 10 Month = 209 Certified 11 Month = 229 Certified 9-Month = 172 Haskell (Year-Rnd)= 212 Non-Certified 10 Mth = 209 Add Snow Days: 1 Minus Holiday/Break 16 "Shane Devenshire" wrote: Hi, Please show us some sample data in the lookup table and then explain, with respect to that sample, what you mean by "count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved" -- If this helps, please click the Yes button Cheers, Shane Devenshire "Denise" wrote: I created a formula to reference a drop-down menu & then based on the value...get value from the selected Lookup Table (column #2). Here's the formula: =IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12 Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10 Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven, 2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified 9-Month",VLOOKUP(L3,Nine, 2)))) What I ultimately need this formula to do is to search the chosen lookup table column based on the entered date and count forward from that date the # of cells with text in them ("Holiday") and then subtract that from the value it retrieved. I am trying to get an accurate count of days an employee will work - holidays. Any help appreciated~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup Formula | Excel Discussion (Misc queries) | |||
Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
VLOOKUP Formula | Excel Discussion (Misc queries) |