+ Reply to Thread
Results 1 to 4 of 4

Autofill formula down rows, but increment column reference by 3

  1. #1
    Registered User
    Join Date
    07-21-2014
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    3

    Autofill formula down rows, but increment column reference by 3

    Hi, I would like to know how to autofill a formula so that as I drag it down a column, the column in the formula will increase by 3.

    I have: E9 =COUNTIFS(Attendance!AC5:AC1000,1,Attendance!E5:E1000,"M")
    I want: E10 =COUNTIFS(Attendance!AF5:AF1000,1,Attendance!E5:E1000,"M")
    I want: E11 =COUNTIFS(Attendance!AI5:AI1000,1,Attendance!E5:E1000,"M")

    I have been searching for hours about this, and I have come up with the offset function, the indirect function, and the index function, etc. etc. etc., and I have watched several tutorials on how to use each of these, but I am still very confused. Any help at all would be appreciated.

  2. #2
    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: Autofill formula down rows, but increment column reference by 3

    Hi and welcome to the forum

    Without a sample file to test this on (and Im not ghoing to try and make 1 up), I cannot put this completely into your formula, but this is the principle you need to follow...

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    1
    2
    3
    4
    2
    1
    3
    2
    4
    3
    5
    4


    A2=OFFSET($C$1,,ROW(A1)*3-3)
    copied down
    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

  3. #3
    Registered User
    Join Date
    07-21-2014
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    3

    Re: Autofill formula down rows, but increment column reference by 3

    Thanks! Okay, but I don't simply want to paste exactly what was in another cell-- I want that offset value to be part of a formula.

    Here is a sample file if it helps. I am concerned with dragging and dropping the formula from E9 down the column (as shown in first post) so that I don't have to edit it manually for hundreds of entries (still have to make the rest of the spreadsheet for the entire year).

    Thanks so much for any help you can give me!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-21-2014
    Location
    Indianapolis, Indiana
    MS-Off Ver
    2010
    Posts
    3

    Re: Autofill formula down rows, but increment column reference by 3

    For clarification, I mean the formula from E9 on the stats spreadsheet.

+ 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. Replies: 17
    Last Post: 06-12-2018, 11:55 PM
  2. Replies: 1
    Last Post: 07-01-2013, 12:14 PM
  3. Copy formula down rows, but increment column reference
    By purplelily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2013, 05:38 AM
  4. Formula to autofill with an evenly spaced increment of rows?
    By FormulaChallenged in forum Excel General
    Replies: 1
    Last Post: 07-19-2005, 03:05 PM
  5. [SOLVED] Relative reference autofill increment other than +1
    By SteveB in forum Excel General
    Replies: 3
    Last Post: 06-14-2005, 03:05 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