different formulas based on different inputs

  • Thread starter Thread starter jeffery
  • Start date Start date
J

jeffery

I have 3 columns: PriceA, PriceB, PriceC.

For each row, If I input one of the prices, I want the other 2 to be
calculated off of the entered price.
Each entered Price, has a different set of calculated formulas.

How do I set this up so whichever one I enter, the others get calculated?

I think this would be an worksheet_change event, but am not sure.

Thanks for the help,

Jeff Gray
 
Assuming those three columns are A, B and C, then basically you would set it
up like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
' Do calculations for Columns B & C when entry is in Column A
ElseIf Target.Column = 2 Then
' Do calculations for Columns A & C when entry is in Column B
ElseIf Target.Column = 3 Then
' Do calculations for Columns A & B when entry is in Column C
End If
Application.EnableEvents = True
End Sub

Of course, this doesn't show an error handling routines your coding may
require.
 
If you do not use a UserForm with some text boxes or labels to do the
calculations, you would have to use event code in the worksheet to trigger
the calculation. Alternatively, you could set up command buttons on the
sheet with macros attached to do the initiate the calculations. But if you
want it to be done upon entry on the sheet, then the Change event is
probably the trigger you are looking for.
 
Back
Top