+ Reply to Thread
Results 1 to 13 of 13

Dragging Across Columns, Incrementing Rows

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Dragging Across Columns, Incrementing Rows

    I have the following formula in cell K4:

    Please Login or Register  to view this content.
    I now need to figure out how to increase the rows for certain references while dragging across columns.

    So I need the formula I posted above to read:

    Please Login or Register  to view this content.
    when I drag it to the next column (L4) and so on and so forth. My columns go to cell OO and the rows go to 78323
    Last edited by Fpob; 02-07-2012 at 10:53 AM.

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    Hello,

    Please try

    =IF(INDEX(Raw_Data!$C:$C,COLUMN()-1)=INDEX(Raw_Data!$B:$B,COLUMN()-1)&"_"&J$3,INDEX(Raw_Data!$L:$L,COLUMN()-1),Master_Data!$B$2)
    Good luck.

  3. #3
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    0EGO,

    I tried your formula and it didn't work.
    Let me explain better, I didn't explain it very well.
    Here is what I need per cell:

    B4: =IF(Raw_Data!C2=Raw_Data!B2&"_Address",Raw_Data!L3,Master_Data!$B$2)
    C4: =IF(Raw_Data!C3=Raw_Data!B3&"_"&$C$3,Raw_Data!L4,Master_Data!$B$2)
    D4: =IF(Raw_Data!C4=Raw_Data!B4&"_"&$D$3,Raw_Data!L5,Master_Data!$B$2)
    E4: =IF(Raw_Data!C5=Raw_Data!B5&"_"&$E$3,Raw_Data!L6,Master_Data!$B$2)

    Is there a formula I can use to drag across 300+ columns?

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    You said the formula was in K4, so that is what I adjusted for. Where is it actually?

  5. #5
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    Sorry, that's my fault. I did copy the formula into K4 and it didn't work. The data starts in B4 and goes to OO4.

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    Didn't work in what sense? (there is no point in me adjusting it until I know that)

  7. #7
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    Aha - I just noticed that the row number for column L is one more than for the other columns, so

    =IF(INDEX(Raw_Data!$C:$C,COLUMN()+8)=INDEX(Raw_Data!$B:$B,COLUMN()+8)&"_"&J$3,INDEX(Raw_Data!$L:$L,COLUMN()+9),Master_Data!$B$2)

  8. #8
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    Sorry, both formulas are not referencing the correct cell in the Master_Data worksheet.

  9. #9
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    According to your example, that cell does not change, so I do not see how it can be wrong.

    Are you copying down rows as well as across columns?

  10. #10
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    I meant to say it's not referencing the correct cell in Raw_Data in the L Column

    Either that or the IF() is coming back false.

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dragging Across Columns, Incrementing Rows

    That formula is fundamentally wrong for your purposes (having seen the file). What you want is something like

    =INDEX(Raw_Data!$L:$L,MATCH($A4&"_"&B$3,Raw_Data!$C:$C,0)+1)

  12. #12
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    Brilliant!

    Thank you so much!

  13. #13
    Registered User
    Join Date
    01-28-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Dragging Across Columns, Incrementing Rows

    Bahh, the formula can't be dragged down!

+ 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