# Incrementing ROW when dragging across columns

1. ## 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.

John

2. ## Re: Incrementing ROW when dragging across columns

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.

3. ## Re: Incrementing ROW when dragging across columns

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

4. ## Re: Incrementing ROW when dragging across columns

Yes, the other two will be static references.

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

5. ## Re: Incrementing ROW when dragging across columns

Thanks Pete - worked great

6. ## Re: Incrementing ROW when dragging across columns

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")))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1