Circular reference workaround needed

[kriedel858]

Looking for a way to have two cells that can either be populated from a simple formula, or based on a user-entered value. This is for a mortgage-loan related application.

Example:

In cell A2 is the purchase price value.

Cell B2 is the loan amount.

Cell C2 is the Loan-to-Value (LTV) ratio

Looking to be able to enter a value in B2 that will return the LTV in C2, while also allowing a value to be entered into C2 that will populate the loan amount in B2.

 

[mikerickson]

You could set Calculation to Manual, but then you'd have the issue of having a formula in C2 and B2, but overwriting them with a value.
VBA would be the only approach.
Do you know VBA?

 

[kriedel858]

I assumed VBA would be needed for this in the form of a macro that kicks in when the cells are exited. I know VBA in a general sense, but have no clue what code to use.

 

[Trebor76]

You could use this event macro on the sheet with the data in cells A2:C2:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Select Case Target.Address
        Case Is = "$B$2"
            Range("C2").Formula = "=B2/A2"
        Case Is = "$C$2"
            Range("B2").Formula = "=A2*C2"
    End Select
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

To install my worksheet event macro follow these four steps:

1. Copy my code above to the clipboard (Ctrl + C)
2. Right click on the relevant sheet and from the shortcut menu select View Code
3. Paste my code from step 1 in to the module (Ctrl + V)
4. From the File menu select Close and Return to Microsoft Excel

HTH

Robert

 

[kriedel858]

Yes! Worked like a charm! THANK YOU!

 

[Trebor76]

Yes! Worked like a charm! THANK YOU!

You're welcome