1

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.

1
  • @pnuts No in two cells side by side.
    – Tomkarho
    Jun 11, 2013 at 9:54

1 Answer 1

1

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 be Interviewed and in the Refers 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 the INDEX() 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 column B1: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.

enter image description here

1
  • I guess I am just envisioning a much larger and more complex list that he wants to be able to add names to without having to make a new drop down list. This allows it to work once sorted.
    – Scheballs
    Jun 12, 2013 at 0:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .