Let us assume that you have four variables in cells A1,A2,A3,A4. As
understand your problem, you want to find out how rest three variable
would be affected if any one of them is changed. In my opinion thi
means that we need to maintain the respective ratio of rest thre
variables same as it was earlier. eg if A1=25, A2=24,A3=25 and A4=2
and we change A1 to say 62.5 A2, A3 and A4 must still maintain thei
earlier proportion (24:25:26) and the new values would be thus A2=12
A3=12.5 and A4=13.

I can suggest procedure

Sub Percent()
TestRow = ActiveCell.Row
TestCol = ActiveCell.Column
NewValue = Cells(TestRow, TestCol).Value
If TestRow = 1 And TestCol = 1 Then
Denominator = [B1] + [C1] + [D1]
[B1] = (100 - NewValue) * [B1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 2 Then
Denominator = [A1] + [C1] + [D1]
[A1] = (100 - NewValue) * [A1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 3 Then
Denominator = [A1] + [B1] + [D1]
[A1] = (100 - NewValue) * [A1] / Denominator
[B1] = (100 - NewValue) * [B1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 4 Then
Denominator = [A1] + [B1] + [C1]
[A1] = (100 - NewValue) * [A1] / Denominator
[B1] = (100 - NewValue) * [B1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
End If

End Sub

You will need initialising OnEntry Event procedure eg
Worksheets(1).OnEntry = "Percent" to execute our procedure "Percent
every time you change values in either A1,A2,A3 or A4. It wil
immediately update rest three cells. This could be nuisance. This wil
not allow you to fill up initial values as you want, as other cells ge
affected as soon as you enter any one cell. And since the cells ar
blank to start with , it might give divide by zero error when you ente
the first cell. May be you would want to execute the proc only after yo
click a button or something instead of other cells recalculating as soo
as you enter any one cell.

A V Veerkar

A V Veerka

--
avveerka
-----------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...fo&userid=3033
View this thread: http://www.excelforum.com/showthread.php?threadid=50857