Automatic data based on a variable

[SCrowe]

Happy New Year everyone!

Here's my scenario:

I would like to enter a vacation code in E5 (V12) and have it automatically enter "VACATION DAY (12 HRS * $15/HR) in K5 and calculate the amount in L5. Of course, there are variables involved here:

V indicates that it is a vacation day.
12 indicates the number of vacation hours to be taken.
The $15/hr comes from the value in I5.

I don't know if this should be handled as a VLOOKUP along with other methods or if this is something that needs to be handled in VB?

Thank you so much for any help you may be able to provide!<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

PAYROLL TEMPLATE (NEW TEST).XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	BI-WEEKLYPAYPERIODFOR11/28/07-12/11/07
2		EMPLOYEE:			SCHMO,JOE			STATUS:		FULLTIME	POSITION:	PARAMEDIC
3		DATE	DAYOFWEEK		HOURSWORKED	LUNCHTAKEN(L)	HOURLYWAGES				OTHEREARNINGS	TOTALOTHERS		COMMENTS
4	WEEK1	11/28/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
5		11/29/07	THU		V12			0:00	$15.00	$-	VACATIONDAY(12HRS*$15/HR)	$180.00
6		11/30/07	FRI
7		12/01/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
8		12/02/07	SUN					0:00	$22.50	$-
9		12/03/07	MON		V8						VACATIONDAY(8HRS*$15/HR)	$120.00
10		12/04/07	TUE										WEEK1TOTAL
11		WEEK1TOTALS					WEEK1HOURLYWAGES			$-	WEEK1OTHEREARNINGS	$300.00	$300.00
12
13	WEEK2	12/05/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
14		12/06/07	THU					0:00	$15.00	$-
15		12/07/07	FRI
16		12/08/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
17		12/09/07	SUN					0:00	$22.50	$-				TOTALPAYCHECK(WEEK1+WEEK2)
18		12/10/07	MON
19		12/11/07	TUE										WEEK2TOTAL	$300.00
20		WEEK2TOTALS					WEEK2HOURLYWAGES			$-	WEEK2OTHEREARNINGS	$-	$-
MEDICS

 

[Glove_Man]

In cell K5 put
=IF(LEFT(E5,1)="V","VACATION DAY (" & MID(E5,2,10) & " HRS * $" & $I$5 &"/HR)","")

In cell L5 Put
=IF(LEFT(E5,1)="V", VALUE(MID(E5,2,10)) * $I$5 ,"")



This can be copied down to the other rows, too.

 

[SCrowe]

Thanks for the love, man! Glove Man!

What if I need to add this same functionality for sick day code? It would work just like vacation day, but if I were to enter S8 as the code it would enter SICK DAY instead of VACATION DAY?

I would also like to add a code of HOLIDAY (this one actually would not use a variable like H8 because a holiday is ALWAYS 8 hours multiplied by the hourly rate, so it would input HOLIDAY (8 HRS * $15/HR). I'm not sure how to add these multiple variables to the formula. Is it even possible?

Thanks again for any help!<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

PAYROLL TEMPLATE (NEW TEST).XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	BI-WEEKLYPAYPERIODFOR11/28/07-12/11/07
2		EMPLOYEE:			SCHMO,JOE			STATUS:		FULLTIME	POSITION:	PARAMEDIC
3		DATE	DAYOFWEEK		HOURSWORKED	LUNCHTAKEN(L)	HOURLYWAGES				OTHEREARNINGS	TOTALOTHERS		COMMENTS
4	WEEK1	11/28/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
5		11/29/07	THU		V12			0:00	$15.00	$-	VACATIONDAY(12HRS*$15/HR)	$180.00
6		11/30/07	FRI
7		12/01/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
8		12/02/07	SUN					0:00	$22.50	$-
9		12/03/07	MON		V8						VACATIONDAY(8HRS*$15/HR)	$120.00
10		12/04/07	TUE										WEEK1TOTAL
11		WEEK1TOTALS					WEEK1HOURLYWAGES			$-	WEEK1OTHEREARNINGS	$300.00	$300.00
12
13	WEEK2	12/05/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
14		12/06/07	THU		S8			0:00	$15.00	$-	SICKDAY(8HRS*$15/HR)	$120.00
15		12/07/07	FRI
16		12/08/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
17		12/09/07	SUN					0:00	$22.50	$-				TOTALPAYCHECK(WEEK1+WEEK2)
18		12/10/07	MON		HOLIDAY						HOLIDAY(8HRS*$15/HR)	$120.00
19		12/11/07	TUE										WEEK2TOTAL	$540.00
20		WEEK2TOTALS					WEEK2HOURLYWAGES			$-	WEEK2OTHEREARNINGS	$240.00	$240.00
MEDICS

 

[Glove_Man]

It's almost always possible!!!!!

Try these two:

K5 =IF(E5="","",IF(E5="HOLIDAY","HOLIDAY (8 HRS * $",IF(LEFT(E5,1)="V","VACATION DAY ("&MID(E5,2,10),"SICK DAY ("&MID(E5,2,10))&" HRS * $")&$I$5&"/HR)")

L5 =IF(E5="","",IF(E5="HOLIDAY",8,MID(E5,2,10))*$I$5)


Note that I cheated a little bit. If there are any codes entered which are not HOLIDAY , V then numbers or S then numbers, they get treated as Sick days...

You can add another set of IF statements but it gets a more complicated.

 

[SCrowe]

Wow, you are tooo good!

However, what do I need to do to prevent the following problem below: (I just need a blank K5 cell if I input just hours worked that day.)

Once that is rectified, that should be all I need to make this perfect!

Thanks again...I really appreciate your help!<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

PAYROLL TEMPLATE (NEW TEST).XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	BI-WEEKLYPAYPERIODFOR11/28/07-12/11/07
2		EMPLOYEE:			SCHMO,JOE			STATUS:		FULLTIME	POSITION:	PARAMEDIC
3		DATE	DAYOFWEEK		HOURSWORKED	LUNCHTAKEN(L)	HOURLYWAGES				OTHEREARNINGS	TOTALOTHERS		COMMENTS
4	WEEK1	11/28/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
5		11/29/07	THU		8			8:00	$15.00	$120.00	SICKDAY(HRS*$15/HR)	#VALUE!
6		11/30/07	FRI
7		12/01/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
8		12/02/07	SUN					0:00	$22.50	$-
9		12/03/07	MON
10		12/04/07	TUE										WEEK1TOTAL
11		WEEK1TOTALS					WEEK1HOURLYWAGES			$120.00	WEEK1OTHEREARNINGS	#VALUE!	#VALUE!
12
13	WEEK2	12/05/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
14		12/06/07	THU					0:00	$15.00	$-
15		12/07/07	FRI
16		12/08/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
17		12/09/07	SUN					0:00	$22.50	$-				TOTALPAYCHECK(WEEK1+WEEK2)
18		12/10/07	MON
19		12/11/07	TUE										WEEK2TOTAL	#VALUE!
20		WEEK2TOTALS					WEEK2HOURLYWAGES			$-	WEEK2OTHEREARNINGS	$-	$-
MEDICS

 

[Glove_Man]

Try these :

K5 = IF(OR(E5="",ISNUMBER(E5)),"",IF(E5="HOLIDAY","HOLIDAY (8 HRS * $",IF(LEFT(E5,1)="V","VACATION DAY ("&MID(E5,2,10),"SICK DAY ("&MID(E5,2,10))&" HRS * $")&$I$5&"/HR)")

L5 = IF(OR(E5="",ISNUMBER(E5)),"",IF(E5="HOLIDAY",8,MID(E5,2,10))*$I$5)


I warned you that other codes would be treated as sick days!!! The OR() means that if it finds an empty cell, or a number, it now returns the empty string "".

Are there any other codes you would try and put in these cells?

 

[SCrowe]

When I put either a number, V code, or S code in cell E5, it doesn't show anything in cells K5 or L5. They're just empty.

 

[SCrowe]

Glove Man,

A 1,000 pardons! I pasted the formulas in the wrong cells! They did indeed work!

THANK YOU SO MUCH!

I owe you big time!

 

[Glove_Man]

Have you recalculated the sheet?

It works on mine.

Just confirming you are using
In cell K5:

=IF(OR(E5="",ISNUMBER(E5)),"",IF(E5="HOLIDAY","HOLIDAY (8 HRS * $",IF(LEFT(E5,1)="V","VACATION DAY ("&MID(E5,2,10),"SICK DAY ("&MID(E5,2,10))&" HRS * $")&$I$5&"/HR)")

and

In Cell L5:
=IF(OR(E5="",ISNUMBER(E5)),"",IF(E5="HOLIDAY",8,MID(E5,2,10))*$I$5)

Don't put the leading k5= and the L5= into the actual formula for the cell. If you do that, you will get FALSE which displays as 0 in some formats I believe. That might be the cause.

 

[Glove_Man]

You're welcome - sorry the previous post crossed in the mail.

See you later.

Philip