On 2002-03-19 19:15, pilot wrote:
Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.
This message was edited by pilot on 2002-03-19 19:17
Pilot,
Thanks providing the sample data & explanation.
I'll assume that A1:C17 in sheet
x houses the data you provided, including labels. I'll further assume that you have a sheet called
Employee, which has 4 as value (your example) in A2 (either computed or inputted).
In B2 enter:
=OFFSET(x!$B$2,MATCH(A2,x!$A$2:$A$17),0)
In C2 enter:
=OFFSET(x!$C$2,MATCH(A2,x!$A$2:$A$17,0)-1,0)
In A3 enter:
=INDEX(x!$A$2:$A$17,MATCH(B3,x!$B$2:$B$17,0))
In B3 enter:
=OFFSET(x!$B$2,MATCH(B2,x!$B$2:$B$17),0)
In C3 enter:
=OFFSET(x!$C$2,MATCH(B2,x!$B$2:$B$17),0)
[ If the formula does not work the way you want it, try
=OFFSET(x!$C$2,MATCH(A3,x!$A$2:$A$17),0)
instead. Both formulas return the same value, but pick out that value differently. ]
A3:C3 should be downwards draggable/copiable.
Alternatively, in
x, name the range A2:A17
Years via the Name box, the range B2:B17
SickLevel, and the range C2:C17
Vacation. Then,
in B2 enter:
=INDEX(SickLevel,MATCH(A2,Years))
in C2 enter:
=INDEX(Vacation,MATCH(A2,Years),0)
in A3 enter:
=INDEX(Years,MATCH(B3,SickLevel,0))
in B3 enter:
=INDEX(SickLevel,MATCH(B2,SickLevel)+1)
in C3 enter:
=INDEX(Vacation,MATCH(B2,SickLevel),0)
[ If the formula does not work the way you want it, try
=INDEX(Vacation,MATCH(A3,Years),0)
instead. Both formulas return the same value, but pick out that value differently. ]
A3:C3, like as above, should be downwards draggable/copiable.
That is what I get in the processing area in
Employee:
{4,232.5,77.5;
7,310,116.25}
Hope this is what you're looking for.
Aladin
This message was edited by Aladin Akyurek on 2002-03-20 14:56