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.