+ Reply to Thread
Results 1 to 6 of 6

Copy a formula based on a pattern

  1. #1
    Registered User
    Join Date
    04-06-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Copy a formula based on a pattern

    Hi,

    I'm trying to copy a simple sum formula across a spreadsheet however when i click and drag i can't get the values right. I've attached an example to try and explain.
    1. I'm trying to calculate 12 months of values on particular reporting dates
    2. So in Cell E6 in have enter the formula =SUM(B2:M2) to calculate 12 months as you can see highlight orange.
    3.This calculates the value total of B2:M2
    4. However i need to calculate this figure annually so the next time would be in I6
    5. At the moment i have enter the figure in manually because when i highlight B6:E6 and drag the value in I6 calculates the values from F2:Q2 (highlight yellow) rather than N2:Y2 (pink next 12 months) (There is no need for any calculations in cells B6,C6,D6)
    6. The next problem is each property has different reporting periods
    7. So F9 is the sum of E3:P3 and then when you click and drag the same issue occurs.

    Does anyone have a solution?Workbook7.xlsx

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Copy a formula based on a pattern

    Have a look at the attached.

    In cells B13 and B14 this formula and filled across....I took it out to column AK.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In cells B16 and B17 this formula and filled across same.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I did it in two rows each because I was not clear on those part of the instructions. Delete whatever you don't want.

    Does this help?
    Attached Files Attached Files

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copy a formula based on a pattern

    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says 2003, but your upload indicates 200 or later.

    OK, try this in B6, copied across...
    =IF(MONTH(B5)=1,SUMIFS($B$2:$AN$2,$B$1:$AN$1,"<"&B$5,$B$1:$AN$1,">="&EDATE(B$5,-12)),"")

    Im not sure how you determine what your "year" is for property 3, but it looks like it starts in qtr2? If so, then adjust the MONTH()=1 to MONTH()=4
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-06-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Copy a formula based on a pattern

    Thanks but not quite there, i've deleted a few things to try and make it easier to explain.

    I've coloured coded the totals i need filled in. Orange Jan23- Dec23 total in E5 Orange, Jan24-Dec24 total in I5 etc. Thanks for the help and i think its me not explain very well.

    I also need the formula to take that data from a different sheet
    Cheers
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copy a formula based on a pattern

    Did you try my suggestion? It worked on both your uploads

    For a different sheet, change it to something like this...
    =IFERROR(SUM(IF(COLUMNS(Sheet1!$A:A)=1,"",IF(MOD(COLUMNS(Sheet1!$A:A),4),"",OFFSET(Sheet1!$B$2,0,COLUMNS(Sheet1!$A:A),1,12)))),"")

  6. #6
    Registered User
    Join Date
    04-06-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Copy a formula based on a pattern

    Sorry missed your original post, and yep that works but i can't get the other one to work on the different sheet, i've attached it again. works alright for property 1 but can't get it working for the other properties.
    Thanks again
    Attached Files Attached Files

+ 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. Macro to insert pattern based on value and select based on pattern
    By CB569 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2015, 12:36 PM
  2. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 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. Copy formula down rows based on pattern
    By briancb2004 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2007, 04:27 PM

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