How do I test a condition in one cell and write to another.

[dorf]

I would like to test a condition in a cell and then write something in
another cell.

For Example my If statement is in cell d23 and this is what I want to do: If
c23=#VALUE! then b23=0.

Is this possible.

 

[Elkar]

No, not possible. A formula in one cell cannot place it's results in a
different cell.

Why not just place the formula in cell B23? Or, if B23 already contains a
formula, just incorporate this into it. Something like:

=IF(ISERROR(C23),0,YourFormula)

HTH
Elkar

 

[Gord Dibben]

You cannot write to another cell using a formula.

You have to have the formula in B23

=IF(ISERROR(C23,0,"whatever")

The only way to write to a cell without a formula is through VBA

Do you want to go that route via sheet event code?

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("C23")
If IsError(.Value) Then
Me.Range("B23").Value = 0
Else: Me.Range("B23").Value = "whatever"
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP