Inserting and retaining an input variable

[ARGT]

I am setting up a spreadsheet to calculate interest on a loan amount. The
interest rate, while somewhat stable, does change (more often than not
nowadays!). At present, the current interets rate is entered into a protected
cell at the top of the page. Then, the sheet will recognize if a transaction
process (listed in column A) is an "Interest Calculation" (rather than a
debit or a credit). If so, then, the current interest rate (obtained from the
input cell) is placed in column J and once a date has been entered (column
B), the interest on the daily balance will be calculated.

PROBLEM is, when I change the interest rate, then all the previous IRs
change to the new value. This is because, I have basically used a formula
(copied in all cells of column J) that, once it recognizes that "Interest
Calculation" is the process (in Column A), it simply retrieves the current
interest rate from the input cell i.e., =IF(A22="Interest Calculation",J$13,"
").

I need a way to "fix" the IR once it has been "loaded" into the calculation
rows of the spreadsheet.

 

[Don Guillett]

One way, of many, would be to have a helper column with the interest rate
for the formulas and whenever the you change a cell in the helper column the
rows below take on that interest rate.
=b22
=b23

 

[ARGT]

Hi Don
Thanks for the reply but I do not know how to implement your suggestion with
my scenario. I would like to attach an example copy of the spreadsheet to
more clearly demonstrate what I want to do but that does not seem possible in
this group.

I'll try to describe it better.

In cell B2 I have typed in the current interest rate.
Row 3 is simply headings for the columns A - G.
Each cell in column A (row 4 onwards) contains a drop down list that gives
the user the option (among others) of choosing "Interest Calculation". Once
that is done, for example in A4, the user adds the date to B4. Recognition of
the text "Interest Calculation" in A4 by the other cells across the row
triggers the interest calculation. The interest rate to be used is
automatically selected from cell B1 and is placed in G4. This is then used in
E4 to calculate the daily compounded interest which is added to the balance
in F3 to produce the new balance in F4. There is also scope for making
payments in column D. Column C is, I guess, a helper column (?) to calculate
the number of days for which the interest is to be charged.
Now, all this works well until I need to change the IR. All the IRs that
have been previously transferred into column G now change to match the new IR
typed in B1. Not surprising!

So, I need to make sure that the IRs that are transferred to cells in column
G remain as they were intended and not change when I use a new IR in B1.

You indicated "many ways" of achieving this??

many thanks

ARGT

 

[Don Guillett]

If desired, send your workbook to my address below. Be sure to copy/paste
snippets of these emails along with a clear explanation with before/after
examples of your desires.

 

[ARGT]

Thanks Don for all your expert assistance. The macros to detect change and
input formulae did exactly what I was aiming for.