CODEWITHSUNDEEP
excelif-statementexcel-formulaoffsetabsolute
Blitz
Blitz

Reputation: 1

Excel - Absolute formula for every N row

Column J = Review Frequency (Annually, 6 Monthly, Quarterly, Monthly)

Column L = Last Review Date (i.e.: 01/01/2020)

Column M = Review Due Date

Current formula in column M is:

=IF(J3="Annually",DATE(YEAR(L3)+1,MONTH(L3),DAY(L3)),IF(J3="6 Monthly",DATE(YEAR(L3),MONTH(L3)+6,DAY(L3)),IF(J3="Quarterly",DATE(YEAR(L3),MONTH(L3)+3,DAY(L3)),IF(J3="Monthly",DATE(YEAR(L3),MONTH(L3)+1,DAY(L3)),"ERROR"))))

The problem is...column J is merged across 7 cells. As soon as I drag the formula in column M down it will just show as "ERROR".

I need to keep the cell fixed on J3 for 7 rows, on the 8th row I need to update the cell reference from J3 to J10.

How do I do this...

Thanks in advance

Upvotes: 0

Views: 43

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

If one has the Dynamic Formula XLOOKUP:

=IFERROR(EDATE(L9,CHOOSE(MATCH(XLOOKUP("*",$J$3:J9,$J$3:J9,,2,-1),{"Annually","6 Monthly","Quarterly","Monthly"},0),12,6,3,1)),"Error")

enter image description here

If one does not have the Dynamic Array formula then using INDEX/MATCH():

=EDATE(L3,CHOOSE(MATCH(INDEX(J:J,MATCH("zzz",$J$1:J3)),{"Annually","6 Monthly","Quarterly","Monthly"},0),12,6,3,1))

enter image description here

Upvotes: 1

Dan
Dan

Reputation: 45762

You could use indirect by replacing all your references to J3 with

INDIRECT("J" & FLOOR((ROW(A1)-1)/7, 1)*7 + 3)

So your formula becomes

=IF(INDIRECT("J" & FLOOR((ROW(A1)-1)/7, 1)*7 + 3)="Annually",DATE(YEAR(L3)+1,MONTH(L3),DAY(L3)),IF(INDIRECT("J" & FLOOR((ROW(A1)-1)/7, 1)*7 + 3)="6 Monthly",...

or you can use choose like Scott Craner did

=IFERROR(EDATE(L9,CHOOSE(INDIRECT("J" & FLOOR((ROW(A1)-1)/7, 1)*7 + 3),{"Annually","6 Monthly","Quarterly","Monthly"},0),12,6,3,1)),"Error")

Upvotes: 1

Related Questions