circular formula

[Wolfmanfafa]

All of my google-fu has failed me. Everything I find says a formula in excel cannot be calculated if anything in the formula refers to itself. I know there is a way to do this. I have seen it done before.

What I want to do is create a simple formula to calculate Watts = Volts x Amps
I have created a table in which each line contains all 3 columns (watts volts amps) respectively
The voltage will have to be entered by the user, as well as one of the remaining variables.

I want it so when the voltage and another variable (either amps or watts) is entered, excel returns the value of the remaining variable.

Any ideas?

Thanks

 

[Scott T]

You could use a change event to do this.

The code is written with watts in Column A Volts in B and Amps in C and assuming row 1 are headers

right click on the tab with your table and select view code past the code below
when you enter something in column A-C in row 2 or below the code will run

Private Sub Worksheet_Change(ByVal Target As Range)

Dim currow As Long
currow = Target.Row
If currow <> 1 Or Not IsText(Target) Then
    Application.EnableEvents = False
    
    
    If Target.Column = 1 And Range("B" & currow) <> 0 Then Range("C" & currow) = Range("A" & currow) / Range("B" & currow)
    If Target.Column = 2 And Range("A" & currow) <> "" Then Range("C" & currow) = Range("A" & currow) / Range("B" & currow)
    
    If Target.Column = 3 And Range("B" & currow) <> 0 Then Range("A" & currow) = Range("B" & currow) * Range("C" & currow)
    If Target.Column = 2 And Range("C" & currow) <> "" Then Range("A" & currow) = Range("B" & currow) * Range("C" & currow)
    
    Application.EnableEvents = True
End If
End Sub

The file will need to be saved as a macro enabled file type such as XLSM

 

[BrianJN1]

Withdrawn - The reply above may actually contradict what I was posting.

 

[Wolfmanfafa]

I thought I was fairly proficient with excel. Not anymore!

I haven't really dealt with macros before, other than basic stuff covered in intro excel (ITM 105 was the title of the college course)

I copied and pasted the code. Then, I changed A,B,C to B,C,D because column A is the name of the appliance. Then, I entered a value in A7 (row 1 is my title. row 2 is my table headers. rows 3-6 had manual entries already entered)

I got a "Compile Error: sub or function not defined"
IsText was highlighted like this:
If currow <> 1 Or Not IsText(Target) Then

Is this because row 1 is not my table headers row?

 

[Scott T]

Try changing it to

If currow <> 1 Or Not Application.IsText(Target) Then