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

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

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

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

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 -

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 -

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 -

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 -

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