CODEWITHSUNDEEP
excelexcel-formula
Mariel
Mariel

Reputation: 103

Find Match In Header & Offset To X Number Of Columns

I am trying to find a way to populate an amortization schedule as per below.

Amort schedule

The formula I am using currently is:

=IF($B2=$1:$1,$A2/$C2," ")

However, this only fills the first point ("start point"), what I am trying to do is offset this based on the amortization period. The desired result is:

enter image description here

Is this possible with a formula - or do I need to try my luck with VBA?

Upvotes: 1

Views: 35

Answers (1)

Michael
Michael

Reputation: 4828

Put in these formulas:

D2: =IF(D$1=$B2,$A2/$C2,"")

E2: =IF(E$1=$B2,$A2/$C2,IF(COUNT($D2:D2)<$C2,D2,""))

D2 gets filled down in just the D column.

E2 gets filled right across all remaining columns and then filled down.

Upvotes: 2

Related Questions