connecting formulas

[Chris]

I’m working on a Excel sheet where I have to connect two formulas as follows:

A1 and B1 have together a value of 20%. if A1 is 12%, B1 should
automatically shows 8% and vice versa. I tried it with A1: =0.2-B1; B1:
=0.2-A1 but its not working properly. Could someone please help me?

Thanks in advance!

 

[Jacob Skaria]

In cell A1 enter 20% and in cell B1 enter formula

=0.2-A1

 

[Chris]

This would work in one direction. is it also possible for both directions so
i could change percentages in A1 or in B1?

Thanks,
Chris

 

[Eduardo]

Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,

 

[Roger Govier]

Hi Chris

You can't do that as it creates a circular reference, as each cells
value refers back to itself.
Equally, you cannot enter a value into a cell, and have a formula
residing there.

If you put values say 16 in A1 and 4 in B1
and then put in A2
=0.2*A1/(A1+B1)
and in B2
=0.2*B1/(A1+B1)

the values in A2 and B2 will adjust so that they always sum to 20%

 

[Chris]

ok, so it's not possible...thanks anyway
Chris

Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,

 

[Luke M]

Not possible with just XL...if you want to use VB, you could try this.
Right click on sheet tab, view code, paste the following in:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
'If not changing A1 or B1, or if you change a group of cells
'then do nothing
If Intersect(Target, Range("A1:B1")) Is Nothing Or _
Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target.Address = Range("A1").Address Then
'Formula that controls B1
Range("B1").Value = 0.2 - Range("A1").Value
Else
'Formula that controls A1
Range("A1").Value = 0.2 - Range("B1").Value
End If
Application.EnableEvents = True
End Sub
'=============

Back in XL, you can then change A1/B1, and the other cell will change
accordingly.