Back calculation (MS Excel 2000 SP3)

The attachment is an example of part of a worksheet I created six years ago to assist a son in his business. With it he can calculate a rental for retail premises ringing the changes and as well as detailed calculation working backwards through the target cells. The example is entirely a figment of my mind and the rates are not representative. In the working situation the fill color cells would be protected as they contain formulas and the reconciliation in cell E29 would not be present.

I am presently being asked to provide a facility such that the rental in cell E21 can be changed and the rate in cell D5 automatically recalculated. I am unable to do this as evidently cell E21 is protected and even if it were not protected and it were possible to type in another figure the formula it contains would be lost destroying the functionality of the worksheet. I could provide a second column to replace the target cells but this is not wanted. I am told that professional competitors have such a facility but also there are good reasons for having it apart from keeping up with the opposition!

Is this possible with programming not that I want to write one as it is beyond my capabilities. (And I am not asking for anyone to do it for me! If needs be son will have to employ a professional to do this for him.) All I want is to know if/how it could be done.

To sum up the calculation needs to treat the contents of cells D5 and E21 as variable by calculation and back calculation in relation to each other and the figures for the other floors treated as fixed even though they too could be changed manually if required. I sincerely hope I am making sense as I can't think of any better to come for good advice. {8:-))

Re: Back calculation (MS Excel 2000 SP3)

Excel has a built-in tool for this: Tools | Goal Seek:
- 'Set cell' should be set to E21.
- 'To value' should be set to the desired value for E21.
- 'By changing cell' should be set to D5.
Click OK to start the goal seek.
If the 'To value' is reasonable, Excel will calculate the new value for D5.
You can accept the new value by clicking OK, or restore the original situation by clicking Cancel.