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
Bookmarks