+ Reply to Thread
Results 1 to 8 of 8

arithmetic sequence with Rows function

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    arithmetic sequence with Rows function

    Hello, I am trying to figure out how to write my formula with Rows function to add 12 rows to the formula when dragging down.
    e.g. =INDIRECT("Gantt!$B$12") I need to change to INDIRECT("Gantt!$B$24") next B36, B48 etc..
    I understand this should work =Rows(A$4:A12)*$C$1 with my incremental difference of 12 being in C1.
    But I don't understand how to combine the two formulas.
    thanks

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    667

    Re: arithmetic sequence with Rows function

    So, If I understand what you said:
    =Rows(A$4:A12)*$C$1 will return the row number you want (12,36,48...)
    So: =Indirect("Gantt!" & Rows(A$4:A12)*$C$1)

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,176

    Re: arithmetic sequence with Rows function

    Try this ...

    =INDIRECT("Gantt!B"&ROWS($A$1:A1)*$C$1)

    Or:

    =INDEX(Gantt!B:B,ROWS($A$1:A1)*$C$1)

  4. #4
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: arithmetic sequence with Rows function

    CrimeDog, I get a #ref error with that one.

    =Indirect("Gantt!B"&ROWS($A$1:A1)*$C$1) is getting me the output I need.
    thanks very much

  5. #5
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: arithmetic sequence with Rows function

    One more thing I can't get working with this.
    It works well when my one column starts on B12 and is incremental in 12 rows.
    But I have another column that the info starts on D15 and is incremental in 12 rows. That one is messing with me.
    Ganttpic.PNGOutlookpic.PNG

    So in last pic. F3 is looking for the date from first pic(Gantt) cell D15.
    The next job below in F4 would be looking for info from first pic(Gantt) cell D27
    In F4 I have
    =INDIRECT("Gantt!D"&ROWS($F$3:F4)*$G$1)
    But it is not correct
    Last edited by kriminaal; 01-25-2017 at 12:50 PM.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,414

    Re: arithmetic sequence with Rows function

    Just add 3 onto the end of the formula:

    =INDIRECT("Gantt!D"&ROWS($F$3:F4)*$G$1+3)

    Hope this helps.

    Pete

  7. #7
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: arithmetic sequence with Rows function

    Simple solution.
    Thanks very much.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,414

    Re: arithmetic sequence with Rows function

    Glad to help - thanks for the rep.

    Pete

+ 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. VBA Function Dateadd not working with "w" weekday arithmetic?
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-14-2016, 03:30 PM
  2. large application of a lot of arithmetic to order rows
    By ftngrave in forum Excel General
    Replies: 3
    Last Post: 07-18-2012, 04:51 PM
  3. Excel 2007 : Sequence Function
    By gpx6 in forum Excel General
    Replies: 3
    Last Post: 10-20-2011, 09:39 PM
  4. Delete Rows in Sequence
    By Jakila2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2011, 05:40 AM
  5. Replies: 0
    Last Post: 03-01-2010, 05:23 PM
  6. [SOLVED] I WANT TO ADD A TWO RANDOM CHARACTER SEQUENCE TO A SET OF ROWS
    By XLSUSER in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2005, 06:10 AM
  7. [SOLVED] Count rows not in sequence
    By kamill in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-12-2005, 09:06 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