In this post I am going to use the same formula found in this post: How to automatically create a unique distinct list and remove blanks. I have only changed the cell references to include a full path to the excel file in the formula.

Picture of closed workbook

The range can contain blank cells.

Picture of extracted unique distinct text values


Array formula in A2:

=INDEX('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, MATCH(0, IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 1, COUNTIF(A1:$A$1, ''C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)), 0)) + CTRL + SHIFT + ENTER copy cell and paste it down as far as needed.

An interesting observation is when I evaluate the formula I get #ref errors but the final results are correct.