+ Reply to Thread
Results 1 to 4 of 4

Copy a formula with pattern

  1. #1
    Registered User
    Join Date
    03-01-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Post Copy a formula with pattern

    Hi all! I've googled but still can't find a way to do this.

    I have 2 worksheets, Sheet1 where information is entered and Sheet2 which extracts the info.

    In Sheet2 my formulas are therefore as follows:

    D1=SUM(Sheet1!D7:A8)+SUM(Sheet1!D63:D64)
    D2=SUM(Sheet1!D119:D120)+SUM(Sheet1!D175:D176)
    D3=SUM(Sheet1!D231:D232)+SUM(Sheet1!D287:D288)
    etc...

    I need to continue this pattern downwards for 50-100 rows. Clicking and dragging simply increases the increment by 1 instead of 56 like I would need. Is there any way of simplifying this, as I don't really want to do this manually if possible?!!

    Thanks in advance!

  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: Copy a formula with pattern

    I'm not sure where you get an increment of 56 from those examples - it looks more like 112.

    You can do this using INDEX/MATCH, and as you are only adding 4 cells together you can probably remove the SUM functions entirely. Try this in D1:

    =INDEX(Sheet1!D:D,(ROWS($1:1)-1)*112+7) + INDEX(Sheet1!D:D,(ROWS($1:1)-1)*112+8) + INDEX(Sheet1!D:D,(ROWS($1:1)-1)*112+63) + INDEX(Sheet1!D:D,(ROWS($1:1)-1)*112+64)

    Then you can copy this down as far as you need to.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-01-2018
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Copy a formula with pattern

    Pete_UK you are correct...it would be 112 in this case. I had 56 on my mind because that's the # of lines between each different sum functions. In any case your formula worked perfectly and you have saved me many hours of pure misery!! Thank you SO much!!!

  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: Copy a formula with pattern

    You're welcome - glad to be able to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. [SOLVED] How to copy formula pattern?
    By rayted in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2018, 06:44 AM
  2. Copy Formula pattern
    By miqureshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2016, 10:23 AM
  3. Copy a formula based on a pattern
    By jonnops in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-06-2015, 03:25 AM
  4. 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
  5. 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

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