Recalculate the cells in a column to achieve a set SUM value

[KayCee]

I have attached a simplified version of the spreadsheet that I use to calculate the share of running costs for our club. The cells in the Shared Costs column in the upper table contain a mixer of formulas and fixed values. I have shown the formulas on the worksheet that are used to calculate a multiplication factor 'X' that when applied to the cell formulas in the Shared Costs column in the upper table bring the column total to the Total value in the lower Club and Current Account - Shared Costs table. The formulas (those cells that have a formula) in the upper table are of the form =IF(B25="","",$E$52*$E$53*VLOOKUP(B25,Table1,4,FALSE)).

The adjustment of the Total in the upper table is achieved by clicking on the large arrow to run a macro that I have reproduced to the right of the worksheet. This works without any problems.

However, I wanted to find a more elegant solutions that would adjust the Total of the Shared Costs in the upper table automatically when the Total in the lower Club and Current Account - Shared Costs table changes.

Can this be done?

[SamT]

The apportionment of Shared Costs is worked out by


mutiplying Share per Member by a factor 'X' that will


achieve Total = Shared Costs in the above table.




Work backwards
TotalNumshares = Sum of all members shares (equivalent to number of months this year of a membership)

X = TotaSharedCosts / TotalNumshares

[KayCee]

Work backwards
TotalNumshares = Sum of all members shares (equivalent to number of months this year of a membership)

X = TotaSharedCosts / TotalNumshares

[KayCee]

Thanks for the prompt reply. The formula that you quote only works if all the cells in the upper table have a formula. Some of the cells have a value because that value stays the same if a member leaves the club - otherwise that member would have their shared cost increase for any change of cost in the lower table after they had left the club.

I have uploaded a revised spreadsheet that contains all the features of the original spreadsheet (without real names) so that you can see how it works. For example, if you change a value in the lower table of the Balance worksheet and then click on the arrow which is attached to the macro, you will see how the current worksheet functions. I am happy with the way it works out the shared cost but I was looking for a more elegant way of achieving the same result. I tried an on-change event process (for when the total in the bottom table changes) but that did not work.

I hope that helps to explain what I am trying to achieve.

[KayCee]

Thanks for the prompt reply. The formula that you quote only works if all the cells in the upper table have a formula. Some of the cells have a value because that value stays the same if a member leaves the club - otherwise that member would have their shared cost increase for any change of cost in the lower table after they had left the club.

I have uploaded a revised spreadsheet that contains all the features of the original spreadsheet (without real names) so that you can see how it works. For example, if you change a value in the lower table of the Balance worksheet and then click on the arrow which is attached to the macro, you will see how the current worksheet functions. I am happy with the way it works out the shared cost but I was looking for a more elegant way of achieving the same result. I tried an on-change event process (for when the total in the bottom table changes) but that did not work.

I hope that helps to explain what I am trying to achieve.

I looked on the Internet for a possible solution and came up with the following code for the Balance worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("G36:G46"), Range(Target.Address)) Is Nothing Then


Call CopyPasteFactorX


End If


End Sub


That worked! Problems solved.

In any case, thanks for responding.