Reversible formula

[TCexcel]

Hi all, thanks in advance for taking the time to help.

I have two workbooks because I can't figure out a chain formula that I need to be able to use in reverse.

First workbook:

Row A1 = x
Row B1 = A1*1.25
Row C1 = B1/0.55
Row D1 = C1/0.42

Second workbook (the same formula, just reversed):

Row A1 = B1/1.25
Row B1 = C1*.55
Row C1 = D1*.42
Row D1 = x

I would essentially like to put information in either A1 or D1 and have the other 3 boxes fill out automatically; is this possible or am I stuck using two workbooks...?
Would a circular formula be able to work in my case?

Thanks again for the help.

 

[btomjack]

Why do you have 2 workbooks? Can't you just do this in different columns of the same workbook?

 

[Snakehips]

TCexcel,

Welcome to MrExcel.

When you say two Workbooks I assume that you might mean two worksheets?

I see no way that you can get your formula working forwards and backwards unless you utilise vba.

Perhaps, something like the code below on the basis that you may wish to actually see the formulas in place.
To test the code, right click on a sheet tab and paste the code into the code pane on the right.

Then if you enter a value in column D it will populate C B & A with formulas and vice versa if you enter a value in A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target) Then
MsgBox "Please enter a number!!"
Application.Undo
Exit Sub
End If
If Target.Column = 1 Then
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Target.Offset(0, 1).FormulaR1C1 = "=RC[-1]*1.25"
Target.Offset(0, 2).FormulaR1C1 = "=RC[-1]/0.55"
Target.Offset(0, 3).FormulaR1C1 = "=RC[-1]/0.42"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
If Target.Column = 4 Then
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Target.Offset(0, -3).FormulaR1C1 = "=RC[1]/1.25"
Target.Offset(0, -2).FormulaR1C1 = "=RC[1]*0.55"
Target.Offset(0, -1).FormulaR1C1 = "=RC[1]*0.42"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

Hope that helps in some way.

 

[btomjack]

I don't think you need to use VBA. A simple change of formatting will do just fine. See below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=B1[/TD]
[TD][input information here][/TD]
[TD]=C2/1.25[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=A1*1.25[/TD]
[TD][/TD]
[TD]=C3*0.55[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=A2/0.55[/TD]
[TD][/TD]
[TD]=C4*0.42[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=A3/0.42[/TD]
[TD][/TD]
[TD]=B1[/TD]
[/TR]
</tbody>[/TABLE]


You put the information in cell B1, and the formula runs both ways.

 

[TCexcel]

This code works perfectly, thank you so much.

I was using two worksheets* because I needed to calculate the final price after markups. If the final price was too high/low we would adjust it, but I would then need to know the adjusted cost of the item from the manufacturer (during the meeting, no less).

Having two worksheets was not going to work out with the number of items that were going to be added to the list, and this one post has saved me from scrutiny.

Thank you again.

Drinks are on me if I get to visit the UK