+ Reply to Thread
Results 1 to 3 of 3

creating a selection list out of variable criteria

  1. #1
    Registered User
    Join Date
    10-09-2014
    Location
    luxb
    MS-Off Ver
    2010
    Posts
    2

    creating a selection list out of variable criteria

    Hi,
    I need help for a problem which should get solved by a formula based solution. Hopefully :-)
    I want to create a list of students being absent at the last date I recorded and show this date separately without applying an auto filter. (see attachment)
    I didn’t get it with MATCH or INDEX formula:-(
    Hope someone can help me…
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: creating a selection list out of variable criteria

    These will all require array formula, so be careful as you edit them, they can be tricky.

    A17: =MAX(IF($C$2:$J$9="x", $C$1:$J$1, 0))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    A21: =IFERROR(INDEX(A$2:A$9, SMALL(IF(OFFSET($B$2:$B$9, , MATCH($A$17, $C$1:$J$1, 0), , )="x", ROW(A$2:A$9)-1, ""), ROW(A1))), "")
    (array entered)

    Now copy A21 down and across that small table.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-09-2014
    Location
    luxb
    MS-Off Ver
    2010
    Posts
    2

    Re: creating a selection list out of variable criteria

    Hi,

    Thanks a lot to JBeaucaire, that fits perfect.

+ 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. Creating a simple macro with selection from dropdown list
    By Tescatlipoca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2014, 11:54 AM
  2. Creating a filtered validation list based on selection of another
    By dgtwitch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-21-2010, 09:20 PM
  3. Replies: 1
    Last Post: 12-01-2010, 04:51 AM
  4. Creating a list of buttons to make selection
    By modytrane in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-04-2008, 01:11 PM
  5. Creating a variable validation list
    By Kurt Sasse in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-11-2006, 02:45 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