If Then program i think

[carrguy01]

I am looking to enter a dollar value in cell b7 and in cell b8 the instalment period EI payment number 5. I want my value to be entered into the coresponding instalment period. Payment 5 is e32.

I need the formula so that i can change the values in b7 and b8.
cell a b c e f g
7 lump payment $10000
8 month payment to be made 5

27 instalment monthly intrest extra pay bal total
28 1 108.33 711.62 _____ 234.. 71..

 

[Scott Huish]

Once the payment is in E32, is it supposed to stay there after the value in B7 or B8 is changed?

 

[carrguy01]

Yes it just have to appear on e32 to complete the formula. When aver the values change in the other two cells. The new lump sum is entered the e32 becomes blank and the new coresponseding cell and lump some will appear.

 

[Scott Huish]

Okay, if payment 5 is in column E, I have to assume that payment 1 is in Column A.

In A32:
=IF($B$8=COLUMNS($A$32:A32),$B$7,"")

Copy across as far as necessary.

 

[carrguy01]

The furmula that you gave me works in the first cell with a little alteration. I can't get it to work in the next cell here is the formula as i have it entered know. It comes in as a true statemtent and it should be false
=IF($B$7=COLUMNS(A36),$B$6,"0") column a36 is instalment 2 and it is number format. I also added the 0 at the end for the reason that if i take it out the rest of my sheet does not work i dont think that is the problem.

I may send this directly to you as i am not sure how often you check the message board.

Thanks for the help thus far!!

 

[carrguy01]

Sorry should add this =IF($B$7=COLUMNS(A35:A35),$B$6,"0") this is the fourmula in cell e35 and it works fine

 

[Scott Huish]

The first address in columns should be absolute:

$A$35:A35

 

[carrguy01]

That did not work i ran the evaluation button in the first enter(e35) it comes back as a true statement and the second enter(e36) it comes back a true statement as well and still enters the lump sum payment

Cell B7 is the instalment
cell b6 is the lump sum
cell a35 threw a397 is the intalment number and they are formated number


first entry this is cell e35 =IF($B$7 = COLUMNS($A$35:A35),$B$6,"0")
second entry this is cell e36 =IF($B$7 = COLUMNS($A$36:A36),$B$6,"0")

 

[Scott Huish]

You're changing the formula for the 2nd + cells, don't do that, just copy it across:

Your 2nd formula should read like this after copying the first one across:
=IF($B$7 = COLUMNS($A$35:B35),$B$6,0)

 

[Scott Huish]

Or since you are returning 0 or the amount, you can drop the IF.

=(COLUMNS($A$35:A35)=$B$7)*$B$6

Copy across.