Conditional Cell Calculation

[Mark T.]

Is there a function or formula that can cause an
individual cell NOT to calculate based on the value of
another cell, but have the rest of the spreadhseet
calculate automatically?

 

[A.W.J. Ales]

Mark,

Short answer. No .
Bit longer : At least not as far as I know, but I've learned here to never
say never, so someone else .....

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Tom Ogilvy]

Not really. You can use a formula that involves intentional circular
references so it returns its own value, but that requires setting iterations
under the calculation menu on and can mask real unintentional circular
references. It is an application level setting as well.

 

[Ragdyer]

Maybe we're just talking semantics here.

When you say "Not Calculate", ... WHY ?

Why can't it calculate, and then you *control the return* of the
calculation, where you could perhaps give it the appearance *or* value of a
"not calculated cell"?

 

[Mark T.]

What I actually want is for the cell to preserve the last
value calulated and stop re-calculating when a certain
date value in another field is exceeded. It's just for
that cell though. Any thoughts?

 

[Frank Kabel]

Hi
one idea: you can use the worksheet_change event.
Some assumptions:
- cell A1 is the cell to check
- as an example B1 is a1+10 unless A1 > 10
try the following code (put it in your worksheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
If .Value <= 10 Then
Application.EnableEvents = False
.Offset(0, 1) = .Value + 10 'change this formula to
your needs
End If
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Ragdyer]

Since this is the Functions NG, I assume you're not looking for Coded
solution, but I believe that's probably your best approach.