Dear Friends,
Suppose I have a data like the following:
[TABLE="width: 292"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]FRUITS[/TD]
[TD]QTY[/TD]
[TD]RATE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]GUAVA[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]5395[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]390[/TD]
[/TR]
[TR]
[TD]GRAPE[/TD]
[TD="align: right"]75.25[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]3386.25[/TD]
[/TR]
[TR]
[TD]LEAMON[/TD]
[TD="align: right"]32.5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]487.5[/TD]
[/TR]
[TR]
[TD]LITCHI[/TD]
[TD="align: right"]38.52[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1155.6[/TD]
[/TR]
[TR]
[TD]DATES[/TD]
[TD="align: right"]56.75[/TD]
[TD="align: right"]62.5[/TD]
[TD="align: right"]3546.875[/TD]
[/TR]
[TR]
[TD]CHERRY[/TD]
[TD="align: right"]23.2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]649.6[/TD]
[/TR]
[TR]
[TD]PAPAYA[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]416[/TD]
[/TR]
[TR]
[TD]COCONUT[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GRAND TOTAL[/TD]
[TD="align: right"]21246.83[/TD]
[/TR]
</tbody>[/TABLE]
Total column is simple multiplication of Rate & Qty and Grand Total is normal SUM formula.
"FRUIT" is in A1 & "21246.83" is in D13. In G13 I have put the same figure of Grand Total i.e. 21246.83 manually.
What I need basically a formula in a column or VBA by which if I change the value of G13, Constant the rates the qty of all fruits will be changed automatically on pro rata basis or percentage basis of total qty of fruits.
Thanks in advance.
Regards
RAMU
Suppose I have a data like the following:
[TABLE="width: 292"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]FRUITS[/TD]
[TD]QTY[/TD]
[TD]RATE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]GUAVA[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]5395[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]390[/TD]
[/TR]
[TR]
[TD]GRAPE[/TD]
[TD="align: right"]75.25[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]3386.25[/TD]
[/TR]
[TR]
[TD]LEAMON[/TD]
[TD="align: right"]32.5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]487.5[/TD]
[/TR]
[TR]
[TD]LITCHI[/TD]
[TD="align: right"]38.52[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1155.6[/TD]
[/TR]
[TR]
[TD]DATES[/TD]
[TD="align: right"]56.75[/TD]
[TD="align: right"]62.5[/TD]
[TD="align: right"]3546.875[/TD]
[/TR]
[TR]
[TD]CHERRY[/TD]
[TD="align: right"]23.2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]649.6[/TD]
[/TR]
[TR]
[TD]PAPAYA[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]416[/TD]
[/TR]
[TR]
[TD]COCONUT[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GRAND TOTAL[/TD]
[TD="align: right"]21246.83[/TD]
[/TR]
</tbody>[/TABLE]
Total column is simple multiplication of Rate & Qty and Grand Total is normal SUM formula.
"FRUIT" is in A1 & "21246.83" is in D13. In G13 I have put the same figure of Grand Total i.e. 21246.83 manually.
What I need basically a formula in a column or VBA by which if I change the value of G13, Constant the rates the qty of all fruits will be changed automatically on pro rata basis or percentage basis of total qty of fruits.
Thanks in advance.
Regards
RAMU