Drag formula and range auto changes

[redspanna]

Hi all

The following formula populates cell F2

=SUMIF(Database!$AW$2:$AW$5000,"*November*",Database!L$2:L5000)/($D$5-(COUNTIF(Database!L$2:L$5000,"NA")))

when I drag it down to F3 is there a way that the following parts of the formula (marked in RED will change to next column range

so F3 will hold formula

=SUMIF(Database!$AW$2:$AW$5000,"*November*",Database[COLOR="#FF0000"]!M$2:M5000[/COLOR])/($D$5-(COUNTIF(Database![COLOR="#FF0000"]M$2:M$5000[/COLOR],"NA")))

then F4 will hold formula

=SUMIF(Database!$AW$2:$AW$5000,"*November*",Database[COLOR="#FF0000"]!N$2:N5000[/COLOR])/($D$5-(COUNTIF(Database![COLOR="#FF0000"]N$2:N$5000[/COLOR],"NA")))

etc etc

any help appreciated

kind regards

 

[steve the fish]

Re: Drag formula and ragne auto changes

Try replacing Database!L$2:L5000 with INDEX(Database!$L$2:$N$5000,,ROWS($A$1:A1))

 

[redspanna]

Re: Drag formula and ragne auto changes

Like this?

=SUMIF(Database!$AW$2:$AW$5000,"*November*",INDEX(Database!$L$2:$N$5000,,ROWS($A$1:A1)))/($D$5-(COUNTIF(INDEX(Database!$L$2:$N$5000,,ROWS($A$1:A1)),"NA")))

if so I'm getting a #REF! error

 

[steve the fish]

Re: Drag formula and ragne auto changes

Ref? Do you have a sheet called Database? Does that have ref errors in it?

 

[BarryL]

Re: Drag formula and ragne auto changes

maybe using OFFSET might yield the results?

=OFFSET(Database!L3:L5000,0,ROWS($A$1:A2)-1)

 

[BarryL]

Re: Drag formula and ragne auto changes

maybe using OFFSET might yield the results?

=OFFSET(Database!L3:L5000,0,ROWS($A$1:A2)-1)

so...
=SUMIF(Database!$AW$2:$AW$5000,"*November*",OFFSET(Database!L3:L5000,0,ROWS($A$1:A2)-1)))/($D$5-(COUNTIF(OFFSET(Database!L3:L5000,0,ROWS($A$1:A2)-1)),"NA")))