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

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.

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?