help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michelle - 23 Feb 2007 13:48 GMT

I have several sheets as keys -an employee key, percentage key and a pay
grade key. I hope this makes sense. On the employee key I would like to
pull in paygrade comparisons from the grade key spreadsheet. For example,
the employee earns 5.00/hr, on the FY03 grade he is in the "maximum" grade
and on the FY07 grade he is in the "job worth" grade.

On Grade Key spreadsheet
fy03 fy07
grade low high status grade low high status
5 3 3.99 minimum 5 4.00 4.99 minimum
5 4 4.99 job worth 5 5.00 5.99 job worth
5 5 5.99 maximum 5 6.00 6.99 maximum
5 6 over max 5 7 over max
6 4.5 6.99 minimum 6 5.5 7.99 minimum
6 5.5 7.99 job worth 6 6.5 8.99 job worth

On Employee Key spreadsheet
Employee Grade Per Hour FY03 FY07
Jane Doe 5 5.00 Max Job Worth

Thanks in advance

Reply to this Message

Joel - 23 Feb 2007 15:05 GMT

I wrote a Function to do the job

Put in your worksheet
=GetStatus(3, 5, 5)
where 1st parameter is year (either 3 or 7),
2nd parameter is grade
3rd parameter is salery

You can have cell referce for any of the paraters
=getStatus(A3,B3,C4)

the function is (

Function GetStatus(Year As Integer, grade As Integer, _
Salery As Single) As String

Const StartCell = "A3"
WorksheetName = "Grade Key"

GetStatus = ""

YearColOffset = 0
If Year = 7 Then
YearColOffset = 4
End If

RowOffset = 0
Do While Worksheets(WorksheetName). _
Range(StartCell). _
Offset(RowOffset:=RowOffset, _
columnoffset:=YearColOffset) <> ""

If ((Worksheets(WorksheetName). _
Range(StartCell). _
Offset(RowOffset:=RowOffset, _
columnoffset:=YearColOffset) = grade) And _
(Worksheets(WorksheetName). _
Range(StartCell). _
Offset(RowOffset:=RowOffset, _
columnoffset:=YearColOffset + 1) <= Salery) And _
(Worksheets(WorksheetName). _
Range(StartCell). _
Offset(RowOffset:=RowOffset, _
columnoffset:=YearColOffset + 2) >= Salery)) Then

GetStatus = Worksheets(WorksheetName). _
Range(StartCell). _
Offset(RowOffset:=RowOffset, _
columnoffset:=YearColOffset + 3)

End If

RowOffset = RowOffset + 1
Loop

End Function

> I have several sheets as keys -an employee key, percentage key and a pay
> grade key. I hope this makes sense. On the employee key I would like to
[quoted text clipped - 17 lines]
>
> Thanks in advance

Reply to this Message