mardi 3 mars 2020

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

Aucun commentaire:

Enregistrer un commentaire