How to enter value in calculated field?

[Brett]

I'm calculating money as follows

A1 B1 C1
$100 X [1] = $1000

C1 = A1*B1

The field with [] around it is an input. The user enters 2 for
example and gets $2000. I'd also like the user to enter $2000 into C1
and get 2 in B1. However, once the user enters anything into C1, the
formula is replaced with a constant value. How can I get the above to
work with input for B1 and C1?

Thanks,
Brett

 

[JE McGimpsey]

You'll need an event macro. Put this in your worksheet code module
(right-click on the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("B1:C1")) Is Nothing Then
Application.EnableEvents = False
If .Address(False, False) = "B1" Then
Range("C1").Value = Range("A1").Value * .Value
Else
Range("B1").Value = .Value / Range("A1").Value
End If
Application.EnableEvents = True
End If
End With
End Sub