Auto display from cell validation

I think your problem relates to the named range used in the data validation formula on cell G2 of TESTMAIN2 (where the drop down arrow shows blank cells; though if you scroll up, you will reach the few names currently in the CustomerList sheet BB:BB).

It looks like you've tried to make a dynamicallly sized Named range "EmployeeName" using the formula:

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!$BB$4:$BB$997)-1,1)

Trouble is you have employee data in BB4:BB16 at present but you have a formula in many cells below that so the COUNTA part returns 312 (so the range is way beyond the real data range and the drop down list for G2 shows blanks from row 311 therefore you don't see the names in B4:B16).

If you change your formula for EmployeeName (in Formulas/NameManager) to this:

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!$BB$4:$BB$20)-1,1)

then you'll always see the end of the current employee list in the drop-down of G2.

While this is okay if you only ever have a short list, it probably isn't a viable solution however if you plan to add lots more employees (not sure why they're in a sheet called CustomerList though!).

I suggest instead you delete the formula from all cells in column BB and instead populate those as cells are completed in columns A:C (that way you can state a large range -see below- but the COUNTA will return only the number of rows with employee data). You can do this using the WorksheetChange event (see Don's tutorial Run a Macro When a Specific Cell Changes in Excel for the principle).

In the attached version of your spreadsheet I've:

1) Added this Worksheet event macro to Sheet11 (CustomerList) with comments for your benefit:

Private Sub Worksheet_Change(ByVal Target As Range)
'see if the change was in columns A to C
If Not Intersect(Target, Range("A:C")) Is Nothing Then
    n = Target.row ' get the row and use that to...
    Cells(n, 54) = Cells(n, 2) & ", " & Cells(n, 3) 'populate BB (column 54) from whatever is in B:C so far
End If

End Sub

2) Deleted the formula from BB:BB and changed the EmployeeName range to refer to the whole the column (knowing COUNTA will work now):

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!BB:BB)-1,1)

Now, if you add entries in CustomerList columns A:C, they will appear in the dropdown of G2 on TESTMAIN2 (I've added a couple which you'll need to delete).

Hope this helps. If so, don't forget to Select this answer. If not, others may suggest better ways.

Revision 1: I've updated the file to include (on the CustomerList sheet) a delayed screen garp of what happens if I open the file (with no suggested links) then click on the down arrow in G2 of  TESTMAIN2. Nothing else has changed (and it works for me, suggests employee names withoput any scrolling).