Lets say we have a list of persons that take part in an interview. These persons have basically two roles: interviewer and interviewed. What I want to do is to create a drop down menu that allows only those participants that have the role interviewer.
As in:
Column A | Column B
_________|_____________
person 1 | interviewer
person 2 | interviewed
person 3 | interviewed
person 4 | interviewer
And when I create an excel drop down list it will only show persons 1 and 4 as possible values to insert.
I have an inclination that this would use a combination of IF's but I am in the dark on how to implement this.
Best Answer
You can do this by sorting column B by A-Z and then opening the Name Manager by hitting CTRL+F3. This can be found in the Formulas ribbon tab and the Name Manager under the Defined Names area.
Click on
New...
, name this future list to beInterviewed
and in theRefers to:
field type in this formula.=OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewed",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewed"))
You can repeat these steps for a second list of the interviewers but use this formula.
=OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewer",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewer"))
The names need the absolute sheet reference.Then create your Data Validation List and Allow a List and in the Source field you can hit F3 to bring up your names and select it to have it pasted for you.
No you should have a drop down box with the persons that have interviewer next to them.
The
OFFSET()
funtion is using theINDEX()
function to look through A1:A10 and return a cell reference for us.MATCH()
is being used to supply the row that contains the first occurence of "interviewer". So Index returns the first parameter of Offset. the three commas in a row are because we do not want to move the reference point by any rows or columns.We do however want to change the height! This is the magic because Offset can be used to create a range for us. Our height is calculated by
COUNTIF()
which looks through columnB1:B10
and returns two in your example. Now the Offset functions use the first reference cell and a height of two to build a range for us that happens to contain the names of the interviewers.Again this will only work if you can sort by column B so that all interviewed and interviewer values are in a single range.