+ Reply to Thread
Results 1 to 5 of 5

Formula Copy with Date and cell selection

  1. #1
    Registered User
    Join Date
    02-18-2011
    Location
    Shenzhen, China
    MS-Off Ver
    Excel 2007
    Posts
    10

    Formula Copy with Date and cell selection

    Let's say I have a Formula that uses a date range used in cell D4

    =SUMIFS(G:G,B:B,"Apples",A:A,">"&DATE(2011,5,1),A:A,"<="&DATE(2011,5,31))

    If I want to use the same formula in D5, but now need to show DATE(2011,6,1),A:A,"<="&DATE(2011,6,30))

    How can I do this without having to rewrite the formula over and over in D6, D7. D8. etc.....


    Cell Selection
    Is it possible to write an if/then statement from one worksheet to another worksheet where the if column A Sheet1and row 12 Sheet1 then add value of the cell matching to Sheet2. See attached example.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula Copy with Date and cell selection

    Your sample file bears little (no resemblance) to your SUMIFS formula - if it's unrelated (cell selection) it should be in a separate thread (different question)

    In terms of an adaptive formula re: your SUMIFS example:

    Please Login or Register  to view this content.
    Note: in the above I assumed you meant >= 1st May rather than >=2nd May (ie all May)

  3. #3
    Registered User
    Join Date
    02-18-2011
    Location
    Shenzhen, China
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula Copy with Date and cell selection

    DonkeyOte - thanks for the correction.

    I have attached a new file with more information.

    Sheet1 the cell highlighted in yellow is a copy from the cell above. How can I tell Excel automatically change the date range when copied. For example, the yellow cell should use this formula

    =SUMIFS(G:G,B:B,"Stock",A:A,">="&DATE(2011,10,1),A:A,"<="&DATE(2011,10,31))


    Second, is it possible for Excel to match information in a table on Sheet1 to a table on Sheet2. I can do this manually be using the Paste Link function, but I'm looking for a faster and more efficient way of doing this.

    The main goal is to upload information into Sheet1 Columns A thru I and have Excel do the rest.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula Copy with Date and cell selection

    I offered a suggestion in my prior post though based on your sample you should seemingly be using the values in Column K for the date rather than DATE functions, eg:

    Please Login or Register  to view this content.
    I will also take the opportunity to reiterate the point made in the prior post which is: a thread should have one main question.

    Presently yours has two - SUMIFS & Paste Link - the latter should be a separate thread.

  5. #5
    Registered User
    Join Date
    02-18-2011
    Location
    Shenzhen, China
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula Copy with Date and cell selection

    Okay thank you. I will post only one question from now on. I'll try to work out the problem based on your suggestion. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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