+ Reply to Thread
Results 1 to 5 of 5

Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    3

    Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

    I am trying to copy the cell data from another spreadsheet. The cells they are being copied into are in increments of three rows. The data I am copying from is only in increments of 1 row. The workbook I am copying from is named Sales. The worksheet is named March.

    The function for the first cell (located at F4) is: ='[Sales.xls]MARCH'!$B2
    I want the function for the 2nd cell (located at F7) to be: ='[Sales.xls]MARCH'!$B3

    But if I drag the function, I get: ='[Sales.xls]MARCH'!$B5

    What function can I use to set F7 equel to F4, but increment by 1 cell, instead of 3? What is the specific wording the function in my example?
    Last edited by jwnickman; 03-23-2013 at 10:59 AM.

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

    Re: Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

    Do you mean that you will be copying it into cells that are 3 rows apart (eg cells on rows 1, 4, 7) and you want to have the equivalent of B2, B3, B4 etc in those cells, or will you be copying into all the cells and you want the equivalent of B2, B2, B2, B3, B3, B3, B4, B4 etc in consecutive cells?

    Please advise.

    Pete

  3. #3
    Registered User
    Join Date
    03-23-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

    The first option. But more specifically, I am setting A1 equal to B2. What I need to do after that, is copy and paste A1 into A4 and A7, and have the data in those cells be B3 & B4. The data in A2, A3, A5, A6 etc. are not important.

    Jim
    Last edited by jwnickman; 03-23-2013 at 11:34 AM.

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

    Re: Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

    Okay, put this in A1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then copy it into A4, then A7 etc.

    This implies that the file Sales.xls is open in the same instance as the file you are working on.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    03-23-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Copy Cell Reference formula to 3 rows down but increment formula by only 1 row

    That did it. Thanks for your help.

    Jim

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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