excelexcel-formulaexcel-2013

First Name & Last Name drop down list

When searching for Dropdown list for two columns I get results for dependent drop down lists which is not what I am trying to achieve.

I have a list of first names in column A, and a corresponding list of last names in Column B.

enter image description here

STAFF LIST  |   No of Staff:    |   6
NAME        |   
FIRST       |   LAST        
John        |   Doe     
Rob         |   Zombie      
Kat         |   Canine      
Kitty       |   K9      
Anne        |   van Dam     
Mr          |   T

I can combine them using the following array formula:

{=STAFF_List}

The named formulas are as follows:

STAFF_Count=COUNTA('STAFF LIST'!$A:$A)-3
'-3 due to three header rows

STAFF_List='STAFF LIST'!$A$4:INDEX('STAFF LIST'!$A:$A,STAFF_Count+3)&" "&'STAFF LIST'!$B$4:INDEX('STAFF LIST'!$B:$B,STAFF_Count+3)
'+3 due to three header rows

When I use =STAFF_List in an array formula formula it works perfectly.

enter image description here

When I use =STAFF_List in a data validation list option I do not get any options. Actually I get an error message box first that says the formula evaluates to an error.

What am I doing wrong?

What can I do to make a data validation list that has the full name from the names stored in two columns?

Note I am using excel 2013 and I am trying to avoid the use of a helper column.

Solution

  • Thanks to your question, I was curious to see whether there's any way to avoid using helper columns. While exploring, I was able to find this:

    Microsoft Excel: Advanced spreadsheet modeling using Dynamic Arrays - BRK4001 - YouTube

    now I have a better understanding of array formulas both past and present.

    For data validation, using defined names with INDEX is fine as long as they return a reference to a range.

    My answer here is just to show my trial with Excel 2013, and not trying to use something already said by others (though I have used table lookups before.)

    If using tables (as in Frank Ball's answer) with calculated columns is feasible, you could just define a name, without using INDIRECT (cannot use tables directly, yet):

    Table result

    With named ranges (as cybernetic.nomad suggested)

    Data validation

    Formula with range

    Name manager for range