+ Reply to Thread
Results 1 to 4 of 4

Cell reference increment other than 1

  1. #1
    Registered User
    Join Date
    11-27-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    22

    Cell reference increment other than 1

    This seems like it should be simple but it's proving to be a bit difficult. I need a list to reference cells but with an increment of 7. I'd type them all in, but there's 100 of them. I've found a few threads discussing this but they involve additional math whereas I need a simple reference. The other formulas are way too complex for what I need.

    Basically, I need a formula to do the following:

    A734 = A1
    A735 = A8
    A736 = A15
    ....

    Any help is much appreciated.

    EDIT: I'm doing this because I have a drop-down list using Data Validation referencing a list. But since the cells are 7 spaces apart, the list contains a lot of blank space and the only solution I could come up with was to create a new list referencing the spaced one. Sorry if that sounds confusing.
    Last edited by Nickolai; 12-07-2014 at 04:23 PM.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Cell reference increment other than 1

    In A734

    =INDEX(A:A,(ROW(A1)-1)*7+1)

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Cell reference increment other than 1

    An alternative...

    =OFFSET($A$1,(ROWS($1:1)-1)*7,,)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    11-27-2014
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    22

    Re: Cell reference increment other than 1

    Thank you both very much. Those definitely solved my problem. Rep added.
    Last edited by Nickolai; 12-07-2014 at 10:04 PM.

+ 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. Increment cell reference
    By George.ca in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2014, 11:18 PM
  2. How to increment an absolute cell reference
    By acharletta in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-06-2013, 08:03 PM
  3. [SOLVED] Increment Cell Reference
    By XLVBA in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2012, 08:33 AM
  4. Increment Cell Reference
    By Perry in forum Excel General
    Replies: 4
    Last Post: 03-08-2006, 04:20 AM
  5. A2 cell reference increment
    By cejones in forum Excel General
    Replies: 3
    Last Post: 11-07-2005, 11:45 PM

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