Copy a formula x times sideways based on value in a cell

**fokeiro** · 03-08-2013, 12:50 PM

Hi guys, im not an expert on Excel and i been breaking my head over this , i have the attached workbook, where i have an amount and a formula dividing this amount into payments based on the number in column C ( the original workbook has same but in different Cells but concept is the same ).

Is there a way to copy and paste this formula sideways automatically based in the same number on cell C without doing manually.

Lets say if cell C is 10 payments then copy the formula 10 times starting in column # in the same row but also the formula use this number to divide the amount of the payments.

I dont know if i explained it correctly. Thanks for the help in advance.

**JBeaucaire** · 03-08-2013, 01:10 PM

Put this formula in E3, then copy down and across the table as far as you want. It will only show the payments needed, otherwise blank.

=IF($C3>=COLUMN(A$1), $A3/$C3, "")

Do not edit the part in red, that translates to "payment #1". That part indicates to the formula which payment should appear in that cell.

**Harribone** · 03-08-2013, 01:13 PM

Payments_Harribone.xlsx

See attached file.

There is a new column showing what a monthly payment is and then there are formulas for each month which will auto populate the cells as required. Note the numbers above the months are to determine if that month has a payment required (if you need more than 12 months contiue across with jan -13 feb - 14 etc).

**Kevin UK** · 03-08-2013, 02:14 PM

Hi fokeiro

And also you could use, in E3 copy across and down:
=IF(SUM($D3:D3)=$A3,"",$A3/$C3)

**Harribone** · 03-08-2013, 02:49 PM

Always good to see several solutions to a problem, shows that we all think differently!

Don't forget to mark the thread as SOLVED if these solutions do the job.

**JBeaucaire** · 03-08-2013, 02:50 PM

Thanks for the feedback, Fokeiro. If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.