I think this UDF will do what you want...

Function Bonus(WeekNumber As Long, RowOffsetFromH2 As Long) As Double
Dim CellValue As Double
CellValue = Worksheets("Week" & WeekNumber).Range("H2"). _
Offset(RowOffsetFromH2).Value
Select Case CellValue
Case Is 300
Bonus = 0
Case Is 350
Bonus = 4
Case Is 400
Bonus = 6
Case Is 450
Bonus = 8
Case Is 500
Bonus = 10
Case Else
Bonus = 12
End Select
End Function

Note that the RowOffsetFromH2 argument is numbered starting at 0 (for H2),
then going to 1 (for H3), 2 (for H4), etc.

--
Rick (MVP - Excel)


"SkippyPB" wrote in message
news

Ihave one thing I do in a formula that I don't know or haven't figured
out how to make it work in a function.

The formula looks like this:

IF(AND(299Week1!$H3,Week1!$H3350),4,0))+
(IF(AND(349Week1!$H3,Week1!$H3400),6,0))+
(IF(AND(399Week1!$H3,Week1!$H3450),8,0))+
(IF(AND(449Week1!$H3,Week1!$H3500),10,0))+
(IF(Week1!$H3499,12,0)

Simply put, it looks at the value in Worksheet named Week1 - cell H3
(could be H4, H5, H6 depending, also could be Week2 - 17) ) and if
that number falls within the listed values, it awards points.

Any help putting that into a function that will work with the Week and
Offset shown above would be greatly appreciated.

In other words I'd like to have a function that looks like this:

=Bonus (W, Q)

Where the Function Bonus would look something like:

Public Function Bonus(W, Q)
? the incorporated IFs pointing to the proper cells
End Function

Here W is a number that would form the name of the Worksheet to look
at ("Week" & W) and Q would be the offset Pointing to cells starting
at H2 to pick up the value to analyze.

Any help would be most appreciated.

Thanks.

Steve