Incrementing ROW when dragging across columns

Hi, I've just implemented this function that I've been helped with.

=SUMPRODUCT((RE_Names=Holidays!D5)*(RE_Dates>=Holidays!$E$2)*(RE_Dates<=Holidays!$F$2)*((RE_Shifts="HOLS")+(RE_Shifts="RMHOL")))

I need for Holidays!D5 to be incremented by 1 row when I drag the formula across so in the next one it would be Holidays!D6.
D13 is where the formula starts and is on row 13 going for 30 rows

I've searched and I think =INDIRECT("D"&(COLUMN()+24))) is maybe right? but i cant get it to work with the Holidays! bit.

Can anyone help please?

John

Is it ONLY the Holidays!D5 you want incrimenting by 1 row?
Or will you also want E2 and F2 to incriment?

Try
=SUMPRODUCT((RE_Names=OFFSET(Holidays!D5,COLUMNS($A1:A1)-1,0))*(RE_Dates>=Holidays!$E$2)*(RE_Dates<=Holidays!$F$2)*((RE_Shifts="HOLS")+(RE_Shifts="RMHOL")))

Do Not change the $A1:A1, it is not related to your dataset at all.

Use this as your first term (in the first cell):

(RE_Names=INDIRECT("'Holidays'!D"&COLUMNS($A:E)))

That will return the equivalent of D5, then as you copy it across the E will change to F, G, H etc, thus returning D6, D7, D8.

Hope this helps.

Pete

Yes, the other two will be static references.

I have just tried this and it still drags across the D5 bit to E5 etc.

Thanks Pete - worked great

Originally Posted by jshaw82

I have just tried this and it still drags across the D5 bit to E5 etc.

My Bad, need to lock the original column reference
=SUMPRODUCT((RE_Names=OFFSET(Holidays!$D5,COLUMNS($A1:A1)-1,0))*(RE_Dates>=Holidays!$E$2)*(RE_Dates<=Holidays!$F$2)*((RE_Shifts="HOLS")+(RE_Shifts="RMHOL")))