Range selection List with no blank cells

Try this ARRAY formula...
=IFERROR(INDEX(B:B,SMALL(IF($B$4:$B$15<>"",ROW($B$4:$B$15)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then copy down as needed.

This works for the 1st set of names, as you have shown.

Here's the formula you need in H4 copied down to create your gap-free list:

=IFERROR(INDEX(B$4:B$500,MATCH(ROWS(B$4:B4),INDEX(COUNTIF(B$4:B$500,"<="&B$4:B$500),0),0)),"")

Unlike Ford's formula, this does not require array entering. Use this for your validation list. Remember this list can be anywhere and could be hidden.

Thankyou Ali

I get the following and I don't understand why.
Validation Error.jpg

Seems I did not explain the outcome I am seeking very well.

Attached is an amended WorkBook which chows in Col H what I am hoping for.
That is - a validation cell in each row of column H, which when used, lists all the names with no blank cells so I can select the appropriate name to place in the cell.

Can you please place the correct Formula in the attached Wbook?

H4 use Ali's formula

Data validation use
=OFFSET($H$4,,,COUNTIF($H:$H,"?*"))

One more approach
In H4 Then copied down.Column H is a helper column. If you don't want helper column worksheet event with VBA code is to be used.

=IFERROR(INDEX($B$4:$B$500,AGGREGATE(15,6,ROW($B$4:$B500)/($B$4:$B$500<> ""),ROWS($H$4:$H4))-ROW($B$4)+1),"")

For validation

=$H$4:INDEX($H$4:$H$500,COUNTIF($H$4:$H$500,"*?"))

Originally Posted by VisionSmart

Thankyou Ali

I pasted the formula into a Data Validation List in H4 and I get the below. What am I not doing correctly

I told you to use the formula to create a helper column, not in the data validation box.

Although I didnt explicitly say to put my suggestion in a worksheet cell, then copy down, that's what I meant, as well.

Thanks for letting us know.