connecting formulas

  • Thread starter Thread starter Chris
  • Start date Start date
C

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!
 
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
 
Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,
 
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%
 
ok, so it's not possible...thanks anyway
Chris

Eduardo said:
Hi,
if you enter the % in both cells you will overwrite the formulas you need to
have the formula in one cell,
Click to expand...
 
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.
 
Back
Top