+ Reply to Thread
Results 1 to 8 of 8

Formula to extract nonblanks via dropdown list

  1. #1
    Registered User
    Join Date
    11-18-2019
    Location
    Athens
    MS-Off Ver
    2016 - 2010
    Posts
    9

    Formula to extract nonblanks via dropdown list

    Hello forum,

    I am a long time stalker but i decided it was about time to register and post my own question since i was getting nowhere with what i was trying. I have attached a simulation of the excel file i am working at my job, if anyone can help me. Also i have provided some images just to clarify my point.

    So i am trying to create an excel file with 2 sheets regarding employee's monthly traveling expenses. In the first sheet there will an overview on monthly expenses depending the month that the user is choosing from the drop down list (cell D2) (1st image). The data that was validated in order for the drop list to be created is in sheet2 where i am supposed to keep track of everyone's expenses during each month. As you can see i have filled 6 months just for the sake of the example (2nd image).

    Thus, my aim is to make a functional and easy-to-read overview in Sheet1. That resolves around the drop-down list in D2. What i want is the column D to be filled with the relevant values from Sheet2 (3rd image). For example if i choose "February", i want the values from that month to be extracted in column D in sheet1. That would be an easy task with a Vlookup or Index/Match with D2 as the lookup value. However, since we are talking about hundreds of employees that their expenses change every month, i would like only the cells that contain a value >0 to be extracted (so no blanks and no zeros). That's the first step i would appreciate some help, since i can't find a way to combine such a formula that skips blanks with a drop-down list as the lookup value.

    The second step that probably would give me headaches is to match the extracted values with the relevant IDs (4th image). Let's say that i have chosen in the drop-down list February as my month of choice and column D shows the values that are >0 from sheet2. I am searching for a formula that will pull the right ID that corresponds to the amount at D Column. To be honest, i haven't put much thought on that 2nd step, since i am stuck at the 1st one.

    I do hope i was straightforward and made my point clear. Every piece of advice would be helpful. Thank you in advance.

    Screenshot_4.pngScreenshot_5.pngScreenshot_6.pngScreenshot_7.png
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Formula to extract nonblanks via dropdown list

    Hi. In A3, copied across to C3 and down:

    =IF($D3="","",INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$15)/(INDEX(Sheet2!$D$2:$O$15,,MATCH(Sheet1!$D$2,Sheet2!$D$1:$O$1,0))=Sheet1!$D3),COUNTIF(Sheet1!$D$3:$D3,Sheet1!$D3))))

    and in D3, copied down:
    =IFERROR(INDEX(Sheet2!$D:$O,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$15)/(INDEX(Sheet2!$D$2:$O$15,,MATCH($D$2,Sheet2!$D$1:$O$1,0))>0),ROWS($D$3:D3)),MATCH($D$2,Sheet2!$D$1:$O$1,0)),"")

    Note that this will return the correct details, if there are ties for the amount of money in any given month.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-08-2016
    Location
    kuala lumpur malaysia
    MS-Off Ver
    2010
    Posts
    90

    Re: Formula to extract nonblanks via dropdown list

    Tqvm Glenn

    The above is working.
    Just wondering, is it possible to do it in pivot table with filter at the top i.e. if I am using drop down for January, it will only show the January data

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Formula to extract nonblanks via dropdown list

    wanmuhd. Please don't hijack someone else's thread. Replies get very messy. Start your own thread, but read the yellow banner at the top of this page, first.

    No-one else is to reply to wanmuhd in THIS thread.

  5. #5
    Registered User
    Join Date
    11-18-2019
    Location
    Athens
    MS-Off Ver
    2016 - 2010
    Posts
    9

    Re: Formula to extract nonblanks via dropdown list

    Woah thank you very much. Your formulas do exactly what i wanted to do with that excel. I am gonna give it a try and see if i can understand the logic behind them.
    Last edited by Excelol; 11-19-2019 at 06:25 PM. Reason: typos

  6. #6
    Registered User
    Join Date
    11-18-2019
    Location
    Athens
    MS-Off Ver
    2016 - 2010
    Posts
    9

    Re: Formula to extract nonblanks via dropdown list

    Hello Glenn,

    Would it be possible to explain to me the formula that brings the data while skipping the blanks (the one that is being used in column D). I tried step by step understanding it, but i couldn't by myself, thus, unfortunately, i cannot replicate it by my own on new workbooks. Thank you again for the awesome formulas!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Formula to extract nonblanks via dropdown list

    This one??

    =IFERROR(INDEX(Sheet2!$D:$O,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$15)/(INDEX(Sheet2!$D$2:$O$15,,MATCH($D$2,Sheet2!$D$1:$O$1,0))>0),ROWS($D$3:D3)),MATCH($D$2,Sheet2!$D$1:$O$1,0)),"")


    It is a bit complicated, for sure. The reason for that is that you are searching a 2D array (columns D:O) wanding non-blanks for a particular month (as selected in the DD).

    The bit in red, copied below, selects the correct column from within D:O, corresponding to your chosen month:

    (INDEX(Sheet2!$D$2:$O$15,,MATCH($D$2,Sheet2!$D$1:$O$1,0))
    Cyan: Look for this value,
    Green: in this range,
    Blue: look for an exact match (it returns the column number)
    Black and return the values from the correct column. So the horrible formula simplifies a bit to:

    =IFERROR(INDEX(Sheet2!$D:$O,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$15)/(Range>0),ROWS($D$3:D3)),MATCH($D$2,Sheet2!$D$1:$O$1,0)),"")

    Once again, we need to select the correct column.

    Red bit does that, in the same way as described above.
    Orange: If the correct column range is greater than zero,
    Green: return the row number
    Blue: in ascending order (15), ignoring errors (6)
    Purple: a counter, incrementing by 1 per row,, starting at the smallest row number, then the next smallest row number, etc.
    Red: return the corresponding value from the correct row, selected by the column Match with the month name
    Black If an error is retuned, replace it with a blank.

    Simple????


    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  8. #8
    Registered User
    Join Date
    11-18-2019
    Location
    Athens
    MS-Off Ver
    2016 - 2010
    Posts
    9

    Re: Formula to extract nonblanks via dropdown list

    Damn, i wish i would gain such knowledge on excel as you my friend. Thank you for explaining what you did there. Your clarification helped me a lot. I will mark the thread as read, since i forgot to do it the previous time i replied.

+ 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: 5
    Last Post: 11-15-2018, 11:02 AM
  2. [SOLVED] Extract data from specific table using dropdown list to select table
    By Maggie.S in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-08-2018, 09:21 AM
  3. Formula to extract a list from an existing list ignoring 0 values
    By weso2k in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-16-2014, 10:18 AM
  4. Replies: 0
    Last Post: 08-22-2012, 03:52 PM
  5. Replies: 21
    Last Post: 12-02-2009, 03:27 PM
  6. Date dropdown list and a Time dropdown list in outlook
    By L_ter in forum Outlook Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2009, 02:33 AM
  7. Formula/macro that ignores cells nonblanks
    By pmarques in forum Excel General
    Replies: 6
    Last Post: 02-10-2006, 10:55 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