+ Reply to Thread
Results 1 to 8 of 8

Copy down a column pattern

  1. #1
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Copy down a column pattern

    Hello,

    I need help finding a way to copy down a column pattern. I will be creating list that starts in cell B7 and continues down for 200 rows. I'd like to be able to drag/copy down a column pattern. For example:

    Cell B7 will refer to cell M99 (=M99)
    Cell B8 will refer to cell AH99 (=AH99)
    Cell B9 will refer to cell BC99 (=BC99)

    Essentially, each cell is skipping ahead in column references by 21 columns. Can you help me figure out how best to do this? Thanks so much for the help!
    Last edited by CR78; 05-12-2014 at 04:27 PM. Reason: typo

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Copy down a column pattern

    You'd get better results with an example file, but a combination of offset/match using row and column numbers sounds like the way to go.

  3. #3
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copy down a column pattern

    Quote Originally Posted by gak67 View Post
    You'd get better results with an example file, but a combination of offset/match using row and column numbers sounds like the way to go.
    Thanks so much for the reply. I'm not really sure how to use the offset/matching function you're referencing. Could you give me an example of how the formula would work? I can't upload the excel file I'm using as it includes some confidential information.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Copy down a column pattern

    Should that first one have been M99?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copy down a column pattern

    Quote Originally Posted by ChemistB View Post
    Should that first one have been M99?
    Yes, sorry, I will correct this in my original post.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Copy down a column pattern

    B7
    =OFFSET($M$99,0,(ROWS($A$1:A1)-1)*21) copied down

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Copy down a column pattern

    or
    =INDEX($99:$99,(ROWS($A$1:$A1)-1)*21+13)

  8. #8
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copy down a column pattern

    Thanks so much ChemistB, I really appreciate the help

+ 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. Copy down hypertext cell range pattern & cell reference formula pattern
    By Underexcelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 03:23 AM
  2. How to copy formula and it following a pattern?
    By salvi in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-22-2013, 03:23 AM
  3. Macro to copy down pattern where row and column references change
    By alpha608 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2013, 06:30 PM
  4. copy Merge pattern
    By AnkitGuru in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2012, 11:24 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