Auto balancing of weights

Hi Friends,

I am working on structuring a portfolio where I have the flexibility of changing the weights of a certain criteria in the portfolio and the other criteria getting rebalanced accordingly.

I am attaching a sample sheet.

Let me explain:

I am structuring 9 different portfolios.

I have 4 broad criteria, listed in F4:F7

These 4 broad criteria have been divided into 8 smaller criteria. I have listed the criteria in F12 : F19

I have assigned weights to the 4 broad criteria in cells G4:O7.

The weights for the 8 smaller criteria have been shown in cells G12:O19.

Now for the help....

Assuming I put a number in cell Q4 which is <> G4, the remaining values should automatically get adjusted in a manner such that the remaining numbers in Q5:Q7 should automatically get adjusted. And the same goes for value changed in Q5, Q6, Q7..... Further, if I change the value in Q4 & Q5, the values in Q6 & Q7 should get adjusted automatically....

Basically, I am trying to create a system wherein if I change any 1, 2 or 3 values in any of the cells in Q4:Q7, the other values should get adjusted so that the total weight adds upto 100....

The same is needed to be done in cells T4:T7 and W4:W7

Can someone help me urgently.... Its very important.....

As always, many thanks guys....