Circular reference workaround needed

kriedel858

New Member
Joined
Oct 8, 2014
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
You could use this event macro on the sheet with the data in cells A2:C2:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top