+ Reply to Thread
Results 1 to 6 of 6

Narrow and rank values, then list corresponding data

  1. 11-21-2011, 06:20 AM #1
    Lasse Moe
    Lasse Moe is offline
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Narrow and rank values, then list corresponding data

    Hi all!

    I don`t know if my challenge is resolvable in Excel 2010 the way I want it to, but If anyone can do it, it`s got to be you guy`s

    I have a workbook with 2 sheets. Sheet 1 is where I would like to list top ten opportunities from high to low (Col M in sheet 2, not ranked) based on user data entry date (between >= and <) and user data entry any Salesperson_Name or Department_Name (Col E, F, H, I in sheet 2). These are all unique names, no Salesperson_Name or Department_Name in any Department will appear in more than one column. In addition I would like the formula to only list opportunities with a specific status (Col L in sheet 2). This variable is not data entry but needs to be written in the formula. There are a total of 8 statuses. Let`s use Status_8.

    I will use Microsoft Data and Time Picker Control 6.0 (SP4) as user data entry date. Furthermore I plan to let the user choose Salesperson_Name or Department_Name from a drop-down, but is this the only way? What I really would like is a dynamic text entry field which suggests and narrows possibilities as the user enters data. Can this be done without VBA?

    I`ve attached a workbook with dummy data that I hope illustrates what I`m looking for.

    I greatly appreciate any suggestions.
    Attached Files Attached Files
    Last edited by Lasse Moe; 11-30-2011 at 09:18 AM.
    Reply With QuoteRegister To Reply
  2. 11-21-2011, 10:00 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Narrow and rank values, then list corresponding data

    In O5 of Sheet2, enter formula:

    Please Login or Register  to view this content.
    copied down the table.

    In E10 of Sheet1, enter formula:

    Please Login or Register  to view this content.
    adjust ranges to suit and then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

    in A10 of Sheet1, enter formula:

    Please Login or Register  to view this content.
    in B10:

    Please Login or Register  to view this content.
    in C10:

    Please Login or Register  to view this content.
    in F10:

    Please Login or Register  to view this content.
    adjust ranges to suit and then confirm each with CTRL+SHIFT+ENTER not just ENTER and copy all formulas down down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 11-22-2011, 03:17 AM #3
    Lasse Moe
    Lasse Moe is offline
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Narrow and rank values, then list corresponding data

    Thanks for a well-documented and functional solution to my challenge. I`ve put it into action in a database with >5000 rows, and it works perfect. So, thank you once again

    Is this solvable in Excel:
    Furthermore I plan to let the user choose Salesperson_Name or Department_Name from a drop-down, but is this the only way? What I really would like is a dynamic text entry field which suggests and narrows possible choices as the user enters data.
    Reply With QuoteRegister To Reply
  4. 11-22-2011, 01:59 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Narrow and rank values, then list corresponding data

    Have a look here to see if this would help:

    http://www.contextures.com/xlDataVal03.html
    Reply With QuoteRegister To Reply
  5. 11-23-2011, 09:09 AM #5
    Lasse Moe
    Lasse Moe is offline
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Narrow and rank values, then list corresponding data

    Looks like this will do the trick. I`ll implement on Friday and report back.
    Reply With QuoteRegister To Reply
  6. 11-30-2011, 09:12 AM #6
    Lasse Moe
    Lasse Moe is offline
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Narrow and rank values, then list corresponding data

    Perfect, and thank you all once again for all your help
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1