+ Reply to Thread
Results 1 to 6 of 6

Extracting Bottom 3 Values From a List Selected from a Drop-Down List

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Assuming I have a drop-down box in A1 with a list of the subjects in the grid below, how would I return the bottom three values for that subject in B1, B2 and B3? I'm not able to use a pivot table for this - it needs to be a formula, please. If there are duplicate values, they should not be ignored. My table is longer with values ranging from 3 to 8.

    Excel 2016 (Windows) 32 bit
    JC
    JD
    JE
    JF
    JG
    JH
    JI
    JJ
    JK
    JL
    JM
    JN
    3
    Academic PE
    Art
    Biology
    BTEC IT
    Business Studies
    Chemistry
    Classical Civilisation
    Drama
    Design Technology
    Economics
    English Literature
    English Language
    4
    5
    5
    5
    5
    6
    7
    8
    8
    7
    9
    7
    10
    4
    11
    5
    5
    12
    4
    4
    13
    4
    14
    5
    6
    15
    7
    6
    7
    16
    5
    17
    6
    3
    18
    7
    5
    19
    4
    4
    20
    6
    6
    6
    21
    7
    7
    Sheet: Pupils Dashboard
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    I hate to say this to a moderator but you really should post a sample.

    Paste this formula in B1 and fill down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    I did post a sample in the body of my post. There is no need for an attachment in this case, but thank you for your reply. I'll try your suggestion in the morning.

  4. #4
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Hi,

    I admit that I played football during English classes.
    So if I'm wrong please excuse me but I understood that the last three items are required, not the smaller ones.
    If so, try this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I defined a name "rng" with
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Thanks for your reply. It was indeed the smallest three values that I required.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Extracting Bottom 3 Values From a List Selected from a Drop-Down List

    Quote Originally Posted by mehmetcik View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you - works perfectly.

+ 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. [SOLVED] Active X drop down list - always return to the item in the list that was selected.
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-14-2017, 09:41 AM
  2. [SOLVED] Pop up box appearing when certain values are selected from drop down list
    By messime in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2016, 06:23 PM
  3. Replies: 5
    Last Post: 03-10-2016, 09:32 AM
  4. Replies: 4
    Last Post: 09-09-2015, 03:49 PM
  5. Replies: 2
    Last Post: 02-19-2014, 05:14 PM
  6. [SOLVED] Sum The Values As per the Selected in Drop Down List
    By get2mandar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2012, 03:09 AM
  7. [SOLVED] Drop Down starts at bottom of list
    By John Jahn in forum Excel General
    Replies: 0
    Last Post: 07-19-2011, 11:09 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