Autofill

[kolly]

Hi All, I have a loan repayment schedule on an excel template such that the cell E11 contains a variable number P (i.e E11=P, which is determined by a formular, thus subject to variation), the cell B7=k (where k is a whole number with the condition that k>0 or k= 0). If k=0, I want the cells E13 to E150 on the column E to be automatically filled with the value in cell E11 (E11=P) If k=1, I want the cells E14 to E151 on the column E to be automatically filled with the value in cell E11 (E11=P) If k=2, I want the cells E15 to E152 on the column E to be automatically filled with the value in cell E11 (E11=P)..................................................................................................................................................................................................................................................................................................................... In general, if k=n, I want the cells E{13+n} to E{150+n} to be automatically filled with the value in cell E11 (E11=P) Please I don't want a drag filling of the cells using the mouse, I want the required cells to be automatically filled with the value P in the cell E11 immediately the number k is altered. Help me please. I know this is possible because we have a bunch of talent in the house. Please help. Kolly.

 

[Yard]

Wow, you like your algebra!

Try this in E13 and copied down:

=IF(ROW()>=ROW($E$13)+$B$7,$E$11,"")

 

[Yard]

oops, just realized your second condition:

=IF(AND(ROW()>=ROW($E$13)+$B$7,ROW() < ROW($E$150) + $B$7 + 1), $E$11 , "")<ROW($E$150)+$B$7+1),$E$11,"")<ROW($E$150)+$B$7+1),$E$11,"")< p>

 

[kolly]

Thank you Yard. Let me try it!Kolly

 

[kolly]

Hi,I have tried to use it but it's been giving wrong formular message. Please I dont think we can imput the value p in the formular. Is it wrong to use E11 inplace of p since p is the number in cell E11? I dont think P will work because P is a variable which depends on a formular. Again let me add one more condition - for n>0, I want all the cells from E13 to E{13+n-1} on the column E to be equal to zero. Thank you for the effort. I know we can do it. Thumbs up!!!kolly.

 

[Yard]

Hi,

You've lost me a bit now.

I have tried to use it but it's been giving wrong formular message

What do you mean? What message?

Is it wrong to use E11 inplace of p since p is the number in cell E11?

I don't understand that, sorry.

I dont think P will work because P is a variable which depends on a formular.

It doesn't matter what P is, since P (i.e. the value in E11) will simply be displayed in the cells - its value has no bearing on the calculation.

for n>0, I want all the cells from E13 to E{13+n-1} on the column E to be equal to zero

This conflicts with having the value P displayed in these cells, as per your original request:

I want the cells E{13+n} to E{150+n} to be automatically filled with the value in cell E11 (E11=P)

As it stands, the formula will display in cells E{13+n]:E{150+n} the value in E11, where n = the value in B7. Maybe you can clarify where this should be changed?