I am using Excel 2010:
I have two worksheets:
Worksheet "Lists": contains the list data for the dropdowns
1. Named Range "QualList"- =Lists!$A$1:$A$128 contains a list of qualifications that set up the first dropdown list, which I have done using data validation/List/"Named Range"
2. Named Range "Master"- =Lists!$C$1:$F$1620 contains the master list of Employees, the district on which they work and the qualifications.
Worksheet "Data Entry" has the three columns which contain separate dropdown lists:
1. Column A under "Qual" (as identified above using ="Named Range").
2. Column B under "Dist" which will be dependent on which qualification is selected in the adjacent cell in column A.
3. Column C under "Employee" which will be dependent upon column A and column B. It must go in that order, Column A, Column B, Column C.
The master list has more than one instance of each employee, district and qualification. For example: for the qualification of ASGS, two employees work on district "YICC" and one works on district "SO".
On worksheet "Data Entry", I need to first identify a qualification, which I have done.
There are too many qualifications to try and break all the information out into separate District/Employee lists for each qualification, as this data changes often and is dependent on a manual import from an external source (can't tap into it for auto import).
I have tried the following formula and can get it to work in successive cells, but cannot get it to work in Data Validation with dropdown lists. I can only get it to populate the first instance in the District List.
=INDEX(Master,SMALL(IF(Master=$A$2,ROW(Master)),ROW(1:1)),2)
I have attached a workbook. Any ideas?
Range "QualList"QualDataExample.xlsmQualDataExample.xlsm
Bookmarks