Function OddsEx()

What would make the above function be recalculated? Excel recalculates
functions when a cell on which they depend is changed. It looks only at the
arguments of the function to determine dependencies and your function has
none. If this is appropriate, then you need to make it volatile - a
function that is recalculated each time the sheet is calculated regardless
of dependcies. You do that by doing this

Function OddsEx()
Application.Volatile
' exisiting code
End Function


otherwise, you would modify it to take an argument - something like this
(dependent on Q3)

Function OddsEx(rng as Range)
If rng.Value = "1/1" Then
OddsEx = Range("ab6").Value
ElseIf rng.Value = "21/20" Then
OddsEx = Range("ab7").Value
ElseIf rng.Value = "11/10" Then
OddsEx = Range("ab8").Value
ElseIf rng.Value = "10/9" Then
OddsEx = Range("ab9").Value
ElseIf rng.Value = "6/5" Then
OddsEx = Range("ab10").Value
Else: OddsEx = 99.98
End If
End Function


usage:
=OddsEx(Q3)




--
Regards,
Tom Ogilvy

"Ron" wrote in message
0.131...
Hi all,

I've wrote a custom function, which attempting to see a value and check
what it is, then grab another value and make the [custom function] cell
value the same.

However the function cell doesn't seem to update itself, I have to
double click the cell and click off it to get it to calculate/update.

Function OddsEx() is in cell S3

here's the function..

Function OddsEx()
If Range("q3").Value = "1/1" Then
OddsEx = Range("ab6").Value
ElseIf Range("q3").Value = "21/20" Then
OddsEx = Range("ab7").Value
ElseIf Range("q3").Value = "11/10" Then
OddsEx = Range("ab8").Value
ElseIf Range("q3").Value = "10/9" Then
OddsEx = Range("ab9").Value
ElseIf Range("q3").Value = "6/5" Then
OddsEx = Range("ab10").Value
Else: OddsEx = 99.98
End If
End Function


Cell Q3 has a drop down list of the possibles, and depending on the
selection i.e. "21/20" it goes and gets the decimal value from a list in
cells AB6 thru AB10, the function OddsEx() should be the value
indicated.

It is, but like I say it needs me to double click the cell containing
the custom function and then click off it to update this value.

If any kind person could explain where I'm going wrong and why?

I don't mind ellaborate explanations rather than a quick 'fix' because
I'm teaching myself VBA and want/need to know why I've gone wrong here.

Thanks

Ron