CODEWITHSUNDEEP
excelvbaexcel-formula
Joe B
Joe B

Reputation: 11

Allow two cells to depend on each other

I am trying to write a formula that allows me to calculate a formula that has two variables. I want to be able to enter either variable A and have it calculate B, OR enter variable B and have it calculate A.

Formula to calculate my variables:

NOI is a known value. I want to have separate cells for Value and CAP, and be able to enter data into either cell and have the other cell compute based on my data entry.

I was going to try an IF function with an ISBLANK, but I'm not sure how to do this that won't overwrite the formula.

Upvotes: 0

Views: 4522

Answers (3)

Stuart
Stuart

Reputation: 1

There is a way to do this without macros using the ISFORMULA() function, which is available in Excel 2013 or later. The trick is ISFORMULA() can query the state of a cell without actually evaluating it's value, and so can be used to direct a formula to avoid referring to that cell and causing a circular reference.

To wit:

A1 is Value, A2 is NOI (which is given) and A3 is CAP

A1: =IF(NOT(ISFORMULA(A3)), A2 / A3, "")
A2: 123.456
A3: =IF(NOT(ISFORMULA(A1)), A2 / A1, "")

Both A1 and A3 will remain blank until you enter a value in either one of them, at which time the other will be calculated appropriately.

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

As an alternate approach you can enter the number in cell A1. In Cell A2 State if it is Cap or Value. In Cell A3 place your NOI. Since the formula is essentially the same for both cases you can simply use the following in A4:

=A3/A1

In B4 I would place the following to indicate which value it was:

=IF(A2="Value", "CAP", IF(A2="CAP","Value", "Enter Type in A2"))

However if you absolutely want data entry in A1 or A2 and then have the other cell get calculated then your only approach is VBA as Tim Williams has pointed out.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166331

You can have a formula, or enter a value, but you can't do both. The workaround is to use the Worksheet_change event to monitor each of the two cells and set the other one according to any entered value.

This would go in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Goto haveError:

    If Target.Address = "$A$2" Then
        Application.EnableEvents = False
        Me.Range("A3").Value = 'set value in A3
        Application.EnableEvents = True
    End If
    If Target.Address = "$A$3" Then
        Application.EnableEvents = False
        Me.Range("A2").Value = 'set value in A3
        Application.EnableEvents = True
    End If
    Exit sub

haveError:
    'make sure events are re-enabled if an error occurs
    Application.EnableEvents = True
End Sub

Upvotes: 5

Related Questions