Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
I am creating a tracker for my work time and I am getting fusterated at
creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
Lookup LOOKUP in the help index
-- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
I did look it up, but it only lets me add one at a time, I need it to include
each of them. "Don Guillett" wrote: Lookup LOOKUP in the help index -- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
Here is a better explaination:
Can you do this formula? Blank PTO Unpaid Tardy Occurrence Exhausted all PTO P FALSE FALSE FALSE I need the formula to populate the answer, so on this first row the answer would be P because the rest are false. "Mandy" wrote: I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
The idea is that you set up a 2-column table somewhere with your
codes, like this: PTO P Unpaid U Tardy Occurrence T Exhausted all PTO EP Specialized PTO SP Bereavement B Unscheduled PTO UP PTO Manager Override MO Floating Holiday MO Jury Duty JD FMLA FM NML NM Suppose this occupies X1:Y12. You can have a drop-down in B2 which uses the list in X1:X12 as the source, and then in B3 you would have the formula: =VLOOKUP(B2,$X$1:$Y$12,2,0) and it will return the appropriate abbreviation. Hope this helps. Pete On Sep 3, 6:59*pm, Mandy wrote: I did look it up, but it only lets me add one at a time, I need it to include each of them. "Don Guillett" wrote: Lookup LOOKUP in the help index -- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... * This has to work all- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
Great that worked perfect, how do I make it say O if the return is #N/A
"Pete_UK" wrote: The idea is that you set up a 2-column table somewhere with your codes, like this: PTO P Unpaid U Tardy Occurrence T Exhausted all PTO EP Specialized PTO SP Bereavement B Unscheduled PTO UP PTO Manager Override MO Floating Holiday MO Jury Duty JD FMLA FM NML NM Suppose this occupies X1:Y12. You can have a drop-down in B2 which uses the list in X1:X12 as the source, and then in B3 you would have the formula: =VLOOKUP(B2,$X$1:$Y$12,2,0) and it will return the appropriate abbreviation. Hope this helps. Pete On Sep 3, 6:59 pm, Mandy wrote: I did look it up, but it only lets me add one at a time, I need it to include each of them. "Don Guillett" wrote: Lookup LOOKUP in the help index -- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
Of course. Use:
=if(iserror(VLOOKUP(B2,$X$1:$Y$12,2,0)),0,VLOOKUP( B2,$X$1:$Y$12,2,0)) Regards, Fred. "Mandy" wrote in message ... Great that worked perfect, how do I make it say O if the return is #N/A "Pete_UK" wrote: The idea is that you set up a 2-column table somewhere with your codes, like this: PTO P Unpaid U Tardy Occurrence T Exhausted all PTO EP Specialized PTO SP Bereavement B Unscheduled PTO UP PTO Manager Override MO Floating Holiday MO Jury Duty JD FMLA FM NML NM Suppose this occupies X1:Y12. You can have a drop-down in B2 which uses the list in X1:X12 as the source, and then in B3 you would have the formula: =VLOOKUP(B2,$X$1:$Y$12,2,0) and it will return the appropriate abbreviation. Hope this helps. Pete On Sep 3, 6:59 pm, Mandy wrote: I did look it up, but it only lets me add one at a time, I need it to include each of them. "Don Guillett" wrote: Lookup LOOKUP in the help index -- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
=IF(ISNA(VLOOKUP(B2,$X$1:$Y$12,2,0)),0,VLOOKUP(B2, $X$1:$Y$12,2,0))
Gord Dibben MS Excel MVP On Wed, 3 Sep 2008 13:32:00 -0700, Mandy wrote: Great that worked perfect, how do I make it say O if the return is #N/A "Pete_UK" wrote: The idea is that you set up a 2-column table somewhere with your codes, like this: PTO P Unpaid U Tardy Occurrence T Exhausted all PTO EP Specialized PTO SP Bereavement B Unscheduled PTO UP PTO Manager Override MO Floating Holiday MO Jury Duty JD FMLA FM NML NM Suppose this occupies X1:Y12. You can have a drop-down in B2 which uses the list in X1:X12 as the source, and then in B3 you would have the formula: =VLOOKUP(B2,$X$1:$Y$12,2,0) and it will return the appropriate abbreviation. Hope this helps. Pete On Sep 3, 6:59 pm, Mandy wrote: I did look it up, but it only lets me add one at a time, I need it to include each of them. "Don Guillett" wrote: Lookup LOOKUP in the help index -- Don Guillett Microsoft MVP Excel SalesAid Software "Mandy" wrote in message ... I am creating a tracker for my work time and I am getting fusterated at creating this formula, any help would be apreciated: PTO=P Unpaid=U Tardy Occurrence=T Exhausted all PTO=EP Specialized PTO=SP Bereavement=B Unscheduled PTO=UP PTO Manager Override=MO Floating Holiday=MO Jury Duty=JD FMLA=FM NML=NM These are the words I am working with, I need it to work like this: if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", THEN RETURN "P" in B3 .....ECT... This has to work all- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a formula PLEASE I am going crazy
You shouldn't be able to get #N/A if you use X1:X12 as the source list
in a data validation drop-down in B2, unless B2 is empty, so you could use: =IF(B2="","O",VLOOKUP(B2,$X$1:$Y$12,2,0)) If you are not using that source list, then you could use this to avoid the #N/A: =IF(ISNA(VLOOKUP(B2,$X$1:$Y$12,2,0)),"O",VLOOKUP(B 2,$X$1:$Y$12,2,0)) It does look to me that you are asking for O (upper case o) to be returned, rather than 0 (zero), but if you want zero to be returned then replace "O" with 0 in the formula. Hope this helps. Pete On Sep 3, 9:32*pm, Mandy wrote: Great that worked perfect, how do I make it say O if the return is #N/A "Pete_UK" wrote: The idea is that you set up a 2-column table somewhere with your codes, like this: PTO * * * * * * * * * * * * * * * *P Unpaid * * * * * * * * * * * * * * U Tardy Occurrence * * * * * *T Exhausted all PTO * * * * EP Specialized PTO * * * * * *SP Bereavement * * * * * * * * * *B Unscheduled PTO * * * * * UP PTO Manager Override * *MO Floating Holiday * * * * * * *MO Jury Duty * * * * * * * * * * * *JD FMLA * * * * * * * * * * * * * * FM NML * * * * * * * * * * * * * * * NM Suppose this occupies X1:Y12. You can have a drop-down in B2 which uses the list in X1:X12 as the source, and then in B3 you would have the formula: =VLOOKUP(B2,$X$1:$Y$12,2,0) and it will return the appropriate abbreviation. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP with crazy formula | Excel Worksheet Functions | |||
Formula driving me crazy | Excel Discussion (Misc queries) | |||
crazy triple array formula | Excel Worksheet Functions | |||
Formula is driving me crazy????? | Excel Worksheet Functions | |||
Help I am going crazy with this formula. | Excel Worksheet Functions |