Automatically alter formulae in range

[HarryPrice]

Evening All,

I have a large pricing spreadsheet with a 'results' sheet that schedules each item and their respective quantities / unit rate / total cost. The number of items is a dynamic range and the results sheet is currently created using a userform.

What I'm trying to do is allow the user to over-write certain rates and increase/decrease them as they see fit - but I'd like all other rates to increase/decrease by the necessary amount to ensure the sum of all the costs remains constant.

The formula itself is fairly straightforward - but I would like to add in some VBA code to automatically amend the formulae upon a cell change. Obviously all cells would have to be updated each time the user over-writes a rate - and to be honest I haven't a clue where to start with this...

Hope that all makes sense!

Can anybody offer any guidance?

Thanks in advance

 

[wigi]

Hello

I would not change formulas. Rather, try expressing the formulas based on input cells, and let the user or VBA code play with the input cells.
Do not touch the formulas themselves.

 

[HarryPrice]

Thanks for that - I've ended up putting a new 'ratio' column in for the user to change.

I've got all of this column pointing at one particular 'master' cell, and then some worksheet change event code to goalseek the total value back to what it should be by altering the master cell, and hence all of the unchanged cells in the ratio column.

Works really well actually!

Thanks for your help.