+ 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 Guru 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 2302
    Posts
    38,710

    Re: Cell reference increment other than 1

    An alternative...

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



  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. [SOLVED] 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