+ Reply to Thread
Results 1 to 6 of 6

drag down rows that match column in different sheet, but skip every 3 cells in column

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    drag down rows that match column in different sheet, but skip every 3 cells in column

    My example is in cell T2. I have it set up where i can drag the formula across that row correctly, but I cannot figure out how to drag it down each column the way i want it. for example i want the formula:
    in t2 to start at cell 'option data' c2
    in t3 to start at cell 'option data' c8
    in t4 to start at cell 'option data' c14

    when i drag down the formula it starts t3 at cell 'option data' c3
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: drag down rows that match column in different sheet, but skip every 3 cells in column

    Try

    =INDEX(INDIRECT("'OPTION DATA '!$C" & (CELL("row",T2)*6)-10 & ":$C9999"),COLUMNS('OPTION DATA '!$C:C))

    in T2
    Martin

  3. #3
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: drag down rows that match column in different sheet, but skip every 3 cells in column

    That works!! Thank you very much for the quick response and helpful formula!!

  4. #4
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: drag down rows that match column in different sheet, but skip every 3 cells in column

    do you mind looking at this problem as well?...it is very similar to my earlier post.

    match function inside offset function to autofill a formula to start at end of prev series
    Please see attachment:

    I am trying to set up a formula in AI2 to autofill because when i drag down it references the wrong cells in the tab 'option data'. I previously received help in setting this up in W2, which is similar to what I am trying to do in AI2, excpet I have a match function embedded in AI2 which I cannot figure out how to reference the cells I want to match which are in the tab 'option data' column g

    for example i want to match sheet11ai2, to reference optiondatag2:g4 based on sheet11w2. then for the next row down sheet11ai3 to reference optiondatag8:g10, based on sheet11w3...and so on

    *note in cell w2 i have a defined name set up labeled "xprice"

    Thank you for any help in advance!
    Attached Files Attached Files

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: drag down rows that match column in different sheet, but skip every 3 cells in column

    Try

    AI2

    =INDEX(INDIRECT("'OPTION DATA'!$G" & (CELL("row",AI2)*6)-10 & ":$G"& (CELL("row",AI2)*6)-8 ),MATCH(W2,INDIRECT("'OPTION DATA'!$C" & (CELL("row",AI2)*6)-10 & ":$C"& (CELL("row",AI2)*6)-8 ),0))

    AJ2

    =INDEX(INDIRECT("'OPTION DATA'!$H" & (CELL("row",AI2)*6)-10 & ":$H"& (CELL("row",AI2)*6)-8 ),MATCH(X2,INDIRECT("'OPTION DATA'!$C" & (CELL("row",AI2)*6)-10 & ":$C"& (CELL("row",AI2)*6)-8 ),0))

  6. #6
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: drag down rows that match column in different sheet, but skip every 3 cells in column

    That works great! thank you very much!

+ 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