+ Reply to Thread
Results 1 to 5 of 5

Offset/ Index Not sure

  1. #1
    Registered User
    Join Date
    03-28-2014
    Location
    Fourways
    MS-Off Ver
    Excel 2003
    Posts
    2

    Offset/ Index Not sure

    Hi Guys,

    I need to create a Formula, that returns every 13th cell, in column B, for Instance underneath each other.

    I have a set of Dates, with 13 Values for each date In Collumn B) In Row D1, I need to return the Value in C1, in D2, I need to return the Value in C13, in D3, I need to return the value in C26, in D4, return C39, So every 13th cell value ?

    How would I do this. I am new to excel, and read a Bit on the ofset, the ofset works for the 1st Cell, but not the next (when copied down) uses the Ref C1, and then next one down C2 + 13 for example what is not what I want

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Offset/ Index Not sure

    I can probably help you - you are on the right track as you will probably need offset and the ROW() functions; however, your description is a bit off. You say you want: C1 then C13 then C26 then C39... Note that the first 'skip' is only 12 cells, if you want every 13th then it would be C1 then C14 then C27... It can be done either way, but it is much easier if you truly want to skip 13 cells every time.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    03-28-2014
    Location
    Fourways
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Offset/ Index Not sure

    Hi Yes, Sorry, it will always be the 13th cell

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Offset/ Index Not sure

    For the 1st cell, just make a direct reference = C1
    Then use this, copied down...
    =INDIRECT("c"&ROW()*13-13)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Offset/ Index Not sure

    So, I took it to mean it should be C1, C14, C27...
    I typically use OFFSET, although it has some computation issues.
    in the first cell: =OFFSET($C$1,(ROW()-1)*13,0) then drag down
    I also like FDibbins use of INDIRECT since it avoids the computationally intensive OFFSET, but to meet the numbering criteria it would need to change to: =INDIRECT("C"&1+((ROW()-1)*13)) in the first cell then drag down.
    Last edited by Pauleyb; 04-02-2014 at 08:52 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  2. offset, index
    By step_one in forum Excel General
    Replies: 2
    Last Post: 07-21-2011, 02:10 PM
  3. [SOLVED] Should it be Index or Offset ?
    By colwyn in forum Excel General
    Replies: 2
    Last Post: 10-01-2008, 10:14 AM
  4. Using INDEX instead of OFFSET
    By JK1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2008, 11:07 AM
  5. Using OFFSET in INDEX
    By StanUkr in forum Excel General
    Replies: 6
    Last Post: 02-21-2007, 08:52 AM

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