+ Reply to Thread
Results 1 to 6 of 6

Incrementing ROW when dragging across columns

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    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

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,105

    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. #4
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    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. #5
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Re: Incrementing ROW when dragging across columns

    Thanks Pete - worked great

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Incrementing ROW when dragging across columns

    Quote Originally Posted by jshaw82 View Post

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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