Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help
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 "michelle" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|