+ Reply to Thread
Results 1 to 2 of 2

Alpha Sort Table Column based on Criteria for Validation List

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Alpha Sort Table Column based on Criteria for Validation List

    I have table on the first sheet with columns for [Region], [Employee], [Class], [Misc.], and [Level]. What I am wanting is a validation list without blanks on the second sheet in the highlighted cell, G2, listing employees names that is alpha-sorted and a Level 2 or 3. It needs to be dynamic that if the table changes (names added, deleted, or existing employees change levels), the validation list will also update accordingly.

    You can ignore my attempts, but if interested, I first created a list of all level 2 & 3 employees in column D with an array formula, and then tried applying a simple COUNTIF formula in column C to number the names, which could then be used in column E with a VLOOKUP to sort them... but my problem lies that to account for possible additions to the list, I have blank rows to row 51 in my table and lists on the ValidationLists! sheet, creating unwanted blanks in my drop down list.

    EDIT: Table1 will remain sorted by [Region], so I need a non-VBA solution to sort the level 2 & 3 employees with formulas and helper columns.

    Sample 4.xlsx
    Last edited by HeyInKy; 04-18-2017 at 01:55 PM.

  2. #2
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Alpha Sort Table Column based on Criteria for Validation List

    So.... just for the record if anyone is following, I changed my formula in ValidationLists!C2 to a COUNTIFS, so the counting would begin at '1':

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...and then SupList Named Range formula count only cells with values and not formulas, to:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Works like a charm now!

+ 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] VBA to sort Table based on Dept criteria
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2016, 11:44 PM
  2. Data Validation List based on criteria
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-27-2014, 05:28 PM
  3. [SOLVED] Dynamic Validation List based on Criteria
    By Steve Bowden-Jones in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-01-2014, 03:17 AM
  4. Data Validation based on list & sort & separate
    By Thunderer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2014, 10:47 PM
  5. Creating validation list from table column based on data in another column
    By dreamthrum in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2013, 08:38 PM
  6. Replies: 1
    Last Post: 07-13-2012, 10:11 AM
  7. Replies: 3
    Last Post: 08-21-2011, 08:22 PM

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