+ Reply to Thread
Results 1 to 4 of 4

Formua to retrieve data with multiple criteria and dynamic with Dropdown list

  1. #1
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2010
    Posts
    376

    Formua to retrieve data with multiple criteria and dynamic with Dropdown list

    Hello,

    I have a complicated situation with Excel since I need a formula that can help me SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet and try to help me a formula that can retrieve the same information.

    Thanks a lot Formula Test.xlsx

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formua to retrieve data with multiple criteria and dynamic with Dropdown list

    Plop this into C12 and copy over.

    =SUM(OFFSET('Pull 1'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1))

    This takes the range of A7:A25, and offsets it by the number of columns where it finds a match for the validation, and then sums. The column(a1) part just increments the offset by 0, 1, & 2 to pull the other parts of the group.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2010
    Posts
    376

    Re: Formua to retrieve data with multiple criteria and dynamic with Dropdown list

    Thank you for the formula. I have adjusted your formula a bit to serve my purpose. I have another question. If I have another pull 2 worksheet in consist of more product family, so my dropdown list now expanded. How can I retrieve data from 2 lookup worksheets now. I understand that why I don't put 2 pull worksheet together, this data I retrieve automatically via smartview so it is limited in columns I have to seperate them into 3 different spreadsheet to look up entire product family. I attached a new sample worksheet with new dropdown list. Please help, thanks.Formula Test.xlsx

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formua to retrieve data with multiple criteria and dynamic with Dropdown list

    You can always just sum them together, you just need to be careful that data pull is lined up the same way every time.

    In your attached example, the data begin on row 7 for Pull 1 and row 6 for Pull 2.

    In the perfect world where you force the line to line up, it's a simple matter of repeating the given equation and just changing the tab references, and then also insulating them within IFERRORs to evaluate mismatches to zero.

    B12:

    =IFERROR(SUM(OFFSET('Pull 1'!$A$6,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 1'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$6,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 2'!A:A))),0)

    B13:

    =IFERROR(SUM(OFFSET('Pull 1'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(F1)-1)),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$7:$A$25,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(F1)-1)),0)

    And copy both over.


    For a third, repeat the same process.

    B12:

    =IFERROR(SUM(OFFSET('Pull 1'!$A$6,0,MATCH(Summary!$B$9,'Pull 1'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 1'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 2'!$A$6,0,MATCH(Summary!$B$9,'Pull 2'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 2'!A:A))),0)+IFERROR(SUM(OFFSET('Pull 3'!$A$6,0,MATCH(Summary!$B$9,'Pull 3'!$B$3:$BG$3,0)+COLUMN(A1)-1,COUNTA('Pull 3'!A:A))),0)

+ 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. Replies: 6
    Last Post: 01-23-2013, 02:21 PM
  2. Urgent: Create a Dynamic Dropdown list based on a specific criteria
    By vijaye in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2013, 08:07 AM
  3. Retrieve Data from other sheets using dropdown list.
    By lemuel in forum Excel General
    Replies: 8
    Last Post: 09-28-2012, 09:35 AM
  4. Replies: 0
    Last Post: 02-13-2012, 07:18 AM
  5. Retrieve data using dropdown list using macro
    By lemuel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-15-2011, 11:18 AM

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