Copy linked cells without incrementing rows

Likes Likes:  0
Results 1 to 4 of 4

Thread: Copy linked cells without incrementing rows

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy linked cells without incrementing rows

    I have a sheet that has a tab for each month and a summary tab. The first 6 rows of each of the month tabs have data in up to columns AA.
    In the summary tab, I want to have rows 1-6 linked to rows 1-6 in the January tab.
    Then rows 8-13 linked to rows 1-6 in the February tab, and then so on down the sheet for the whole 12 months.

    Iíve linked the rows for January into the summary tab and was going to copy those 6 rows, paste them into rows 8-13 and then do a find and replace substituting January for February. I intended to use this method to populate the summary.

    When I paste rows 1-6 into 8-13 however excel incrementaly increases the rows. Is there a way to stop that? I know I could manually link each cell but Iím interested to know if the other way is possible.

    Hope that makes sense.

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Location
    United States: Portland, OR
    Posts
    366
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy linked cells without incrementing rows

    If you put "$" in front of the cell reference it will lock the reference:

    Change A1 -> $A$1
    Change A1:C100 -> $A$1:$C$100

    Then drag down the formula and those reference will not change.

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy linked cells without incrementing rows

    I thought of that but thereís quite a lot of cells to manually change unless thereís a way to batch change them?

  4. #4
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,646
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy linked cells without incrementing rows

    ††
    Depending on your formula you may be able to fix the first one and copy accros

    you can highlight the cell reference and use F4 to cycle though.

    Highlight S1:S17
    =sum(S1:S17)

    press F4 and it becomes both absolute reverences, neither the column or row change.
    =SUM($S$1:$S$17)

    press F4 again and now only the rows are locked the column will change
    =SUM(S$1:S$17)

    F4 again and only the columns are locked the rows will change.
    =SUM($S1:$S17)

    F4 again and you are back to
    =SUM(S1:S17)
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com