Hi,

i have written my first user defined function as follows:

Function Budget_Amount(Spread_Method As String, _
Annual_Amount As Currency, _
Budget_Month As Integer, _
Manual_Percent As Currency, _
Manual_Amount As Currency, _
Manual_Override As String, _
Rounding_Month As Integer, _
Rounding_Factor As Integer)

Dim Source_Cell As Range

If Annual_Amount = 0 Then
Budget_Amount = 0
Else
If Rounding_Month = Budget_Month Then
Dim x As Integer
Set Source_Cell = Application.Caller
Budget_Amount = Annual_Amount
For x = 1 To 12
If x < Rounding_Month Then
Budget_Amount = Budget_Amount - _
Source_Cell.Cells(1, x + 1 - Rounding_Month).Value
End If
Next x
Else
Select Case UCase(Spread_Method)
Case "MP"
Budget_Amount = Annual_Amount * Manual_Percent
Case "MA"
Budget_Amount = Manual_Amount
Case ""
Budget_Amount = "Spread Required"
Case Else
If Left(UCase(Manual_Override), 1) = "Y" _
And Manual_Amount < 0 Then
Budget_Amount = Manual_Amount
Else
With WorksheetFunction
Budget_Amount = Annual_Amount * _
.Index(Range("Spread_Percent_" & Budget_Month), _
.Match(Spread_Method, _
Range("Spread_IDs"), 0))
End With
End If
End Select
Budget_Amount = WorksheetFunction.Round(Budget_Amount,
Rounding_Factor)
End If
End If

End Function
-----------------------------------
the function works when I enter it into a cell as follows:

=budget_amount($G13,$H13,I$4,AS13,BH13,BG13,Roundi ng_Month,Rounding_factor)

the named ranges Rounding_Month and Rounding_factor are single cell named
ranges. However, I want to be able to use named ranges for all other
variables being passed to the function, i.e. $G13, $H13, etc... where I would
replace $G13 with the named range Selected_Spread_Method which = "B:B" and
$H13 = Annual_Amount = "C:C"

If I use regular excel functions, I can reference a named range like
Annual_Amount, and it knows to use the amount from the same row as the
formula. How can I get my function to act this way?

things I have tried:
- changing the variable types from String to Range
- then I tried finding the intersection of the range and the caller cell.

Ideas?

thanks

Simon