Why is autofill not incrementing in this case? I have an embedded IF formula =IF

Question

Why is autofill not incrementing in this case?

I have an embedded IF formula

=IF(AND('3 - Projects'!$C$6="Yes",Week1>=$E$29,Week1<=$F$29),"x",IF(AND('3 - Projects'!$C$6="No",Week1>=$E$29,Week1<=$F$29),"n",""))

I've used the $ symbol to anchor certain references. But when I drag/fill across rows, the week# reference does not increment. What is preventing it from incrementing automatically during the drag-fill? I DON'T want the other references to change, but I DO want the week number to increment.

As there are 52 columns (weeks in a year) and 25 rows for the respective projects, I'd really like to not have to manually edit all those cells.

Explanation / Answer

Hi,

Try it this way

=IF(AND('3 - Projects'!$C$6="Yes",INDIRECT("Week" & COLUMN(A1))>=$E$29,INDIRECT("Week" & COLUMN(A1))<=$F$29),"x",IF(AND('3 - Projects'!$C$6="No",INDIRECT("Week" & COLUMN(A1))>=$E$29,INDIRECT("Week" & COLUMN(A1))<=$F$29),"n",""))

The bold bit evaluates as Week1 and when you drag 1 cell to the right it becomes Week2

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.