I'm not quite sure as I follow but the cell it refers to isn't
referenced in the actual function but the function I have used is part
of an =if() statement in excel, which uses the number outside the
worksheet to select whether to use the function or not.

Here is my code....namerange shift is another function that I have used
to select cells in a named range that are to the left or right of the
current column as for example, when using Excel's SUM function, excel
tried to SUM the entire name range.

the function is being used inside an IF statement in Excel

=if(DC_Options_Subscriber_Number=1, LRP,
CalculateAverageSubscribersEOP(DC_Subscribers_eop_ Column()))

code:
--------------------------------------------------------------------------------


Function CalculateAverageSubscribersEOP(ByRef EOPSubscriberRange As
Range, ByVal ThisCol As Double) As Double

Dim TestNumber As Boolean
Dim SubRangeValMinus1
Dim SubRangeVal
Dim SubRangeValPlus1

SubRangeValMinus1 = Range(EOPSubscriberRange.Address).Cells(1, ThisCol
- 1).Value
SubRangeVal = Range(EOPSubscriberRange.Address).Cells(1, ThisCol).Value

SubRangeValPlus1 = Range(EOPSubscriberRange.Address).Cells(1, ThisCol +
1).Value

TestNumber =
Application.WorksheetFunction.IsNumber(NameRangeSh ift(EOPSubscriberRange,
-1, ThisCol))

If TestNumber Then
CalculateAverageSubscribersEOP = (SubRangeVal + SubRangeValMinus1) / 2

Else
CalculateAverageSubscribersEOP = (SubRangeVal * SubRangeVal /
SubRangeValPlus1 + SubRangeVal) / 2
End If

End Function


--------------------------------------------------------------------------------

Thanks


--
soteman2005
------------------------------------------------------------------------
soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078
View this thread: http://www.excelforum.com/showthread...hreadid=489778