Linking to column in another Excel file + removing empty cells

You have two independent issues. One is to import or link to the data, and the other is to have a dynamically sized list for Data Validation.

In Working File, use this formula to refer to each corresponding cell in Source File. It assumes both sets of data are in Column A.

='[Source File.xlsx]Sheet1'!A1&""

Copy down for as many rows as desired. The &"" at the end forces conversion to a string so you don't get zeroes for blank cells.

Now set up a named formula to provide the list. Under Formulas, Name Manager, add a name called DataList. The formula should look like this:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"*?*"),1)

DataList is now a list of nonblank elements in column A (this assumes no embedded blank cells).

Then for your dropdown box, use Data Validation, List, and provide =DataList as the list reference.

If any of this is not clear let me know and I can post a simple example.