+ Reply to Thread
Results 1 to 8 of 8

Retrieving Data Based on Two Criteria and Two Worksheets

  1. #1
    Registered User
    Join Date
    08-25-2011
    Location
    So CAl
    MS-Off Ver
    Excel 2007
    Posts
    7

    Retrieving Data Based on Two Criteria and Two Worksheets

    (I cross posted this in the 2007 Subforum so moderators please feel free to delete the other if necessary, this section seems to generate more traffic) With that said.


    Hi everyone, new here and thanks in advance for any help you can offer.

    What I am trying to do is retrieve data from my second worksheet "Expense" into my first sheet, "Sheet1" only if the account number and date match, if else leave it blank.

    So for example

    I want Data to populate if the account number in Sheet1 Row K25 matches the same account number on my "Expenses" Sheet and only if the same date matches.

    So based on the attached images below I would like Cell "AL25" to populate the data from expenses sheet which would be from cell "AO24" but also be dynamic.

    Again any help you guys can offer is greatly appreciated.
    Attached Images Attached Images
    Last edited by ERabbit; 08-26-2011 at 06:01 PM.

  2. #2
    Registered User
    Join Date
    08-25-2011
    Location
    So CAl
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    Here are a couple formulas I'm trying if it helps

    [=SUMIF(Expenses!$K$24:$K88,Sheet1!$K$33,Expenses!$K24:$K88)&(Expenses!AO$21=Sheet1!AL$23)]

    But this is returning "0True"

    also using

    [=SUMIF($K$25:$K$315,Expenses!$K24,Expenses!AO$24:AO$88)]

    This populates the correct number the problem is it doesn't check for date so it doesn't dynamically work acorss the sheet (it will pull Q3's data for September because the Cell References are not matched up.)

  3. #3
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    Your data appear to be in order by month moving across both sheets, so you could use SUMIF(Expenses!$K:$K,$K33,Expenses!AO:AO) in cell AL33 of Sheet1. That would be the lookup for October, and then copying that across and down would work. But if you might change the order of the columns or insert a blank one in either place, you should use SUMIFS to check for both a atch in column K and a match in the date.

  4. #4
    Registered User
    Join Date
    08-25-2011
    Location
    So CAl
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    The sumif works but will not skip cells for instance when Q3 appears it throws off the formula.

    This formula is working except it is kicking the cell count back by 13

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    Why are you using MATCH? That might be what's causing your error. Any way you can attach your spreadsheet or at least an excerpt from it? Much easier to diagnose a live spreadsheet rather than a picture. Thanks.

  6. #6
    Registered User
    Join Date
    08-25-2011
    Location
    So CAl
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    Thanks for your time Bentley Bob

    Attached is an example spreadsheet of what I've been working with. The reason I'm trying to use Match is because I want the formula on the first sheet to skip any cells on the second sheet that are by Quarter or by Year total.

    You can see my two different formulas on the first sheet in October and November. The formual in November works fine when we are forecasting but if I was to try and drag it to January 2012 it would populate the total from expenses for Total 2012.
    Attached Files Attached Files

  7. #7
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    You were very close, but your MATCH was not recognizing that the range doesn't start immediately after Column K, so you need to add 18, as in the attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-25-2011
    Location
    So CAl
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Retrieving Data Based on Two Criteria and Two Worksheets

    You were exactly right, that works perfectly, thanks!

+ 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