+ Reply to Thread
Results 1 to 6 of 6

Need help figuring out a way to copy formula down column in a pattern?

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Minnesota, USA
    MS-Off Ver
    2016
    Posts
    7

    Unhappy Need help figuring out a way to copy formula down column in a pattern?

    Hi there,

    I don't know how to phrase this question but I'm hoping someone could help me figure out a way to copy a formula down columns using a pattern of different cell references. I have over 50 categories in my data set and each category consists of 13 columns - 1st and 2nd columns are hidden, 3rd column is visible, 4th 5th 6th 7th 8th are hidden, 9th 10th 11th 12th 13th are visible. I need help figuring out a way to copy down a formula that references each of the 50 categories separately..

    I'm sorry for the terrible explanation... I would reference the file attached to actually understand what I'm trying to do. Row 2 is the formula that I'm trying to copy down for each category (only 2 categories in the example file) and row 1 is what I would ideally hope the result would be.

    Thank you!
    Attached Files Attached Files

  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,604

    Re: Need help figuring out a way to copy formula down column in a pattern?

    You could put this formula in cell C1:

    =$J3&C4

    and then you could copy this and paste it into cells J1 to N1.

    Similarly for cell P1:

    =$W3&P4

    and copy this into W1:AA1

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-18-2018
    Location
    Minnesota, USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Need help figuring out a way to copy formula down column in a pattern?

    I have more than 50 of these categories... I feel like there should be a more efficient way to do this then typing in the formula and copy and pasting for each category.

    There are a couple of other spreadsheets that I need to create that are similar to this one, that's why I'm posting to see if anyone knows an efficient way to create a pattern with formulas.

  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,604

    Re: Need help figuring out a way to copy formula down column in a pattern?

    Maybe you should re-attach a more realistic sample workbook.

    Pete

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Need help figuring out a way to copy formula down column in a pattern?

    Please try at C1 and copy to the right

    =IF((MOD(COLUMNS($C1:C1)-2,13)>5)*(MOD(COLUMNS($C1:C1)-2,13)<>11),OFFSET(C3,,7-MOD(COLUMNS($C1:C1)-1,13))&C4,"")

  6. #6
    Registered User
    Join Date
    12-18-2018
    Location
    Minnesota, USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Need help figuring out a way to copy formula down column in a pattern?

    Thank you that worked perfectly!!! I appreciate your 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. Help figuring out how to automate a pattern
    By tvwright in forum Excel General
    Replies: 13
    Last Post: 06-26-2015, 02:34 PM
  2. [SOLVED] Copy down a column pattern
    By CR78 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2014, 04:55 PM
  3. 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
  4. Figuring out a pattern
    By tgraves18 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2013, 08:25 PM
  5. 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
  6. Figuring a formula to add and then subtract certain cells in one column
    By countryred in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 06:48 AM

Tags for this Thread

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