I have following data in my excel, attached:
Sheet "Seat_Pool":
Columns:
Seat_No: Distinct entries, with no repeats
Group_Head: Head of the group to whom Seat belongs.
Status: Status of a seat VACANT or OCCUPIED.
In reality this list 350+ and always growing, Status changes all the time but seat_no & group_head mapping rarely changes.

In Sheet "Seat_Assignment":
What I want is that "Seat_No" Column (Column B), should have a Data Validation list dropdown based on Group_Head=Harry and Status=Vacant.

I believe my approach should be in following order:
1. Auto-Copy or Auto-Generate seat_no of matching criteria (Group_Head=Harry and Status=Vacant), in some column. Lets Call it HARRY_VACANT_LIST
2. Sort HARRY_VACANT_LIST
3. Remove Duplicates
4. Give name definition to HARRY_VACANT_LIST, make this as dynamic so as to avoid blanks.
5. Use this new name definition in Data Validation for Seat_No

Limitations:
1. Can't add new columns in any sheet.
(Have seen earlier example on this forum at http://bit.ly/vZa6mI, didnt really like the idea of having a new column C called Validation_KEY)
2. Can't change order of column in any sheet - I am already using VLOOKUP at many places
3. Will prefer a NON-MACRO approach.

Sample File Attached:
paragb_seat_sample.xls