Data Validation - List - Miscellaneous

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Hello,

I have two questions:

1) I would like to validate using a drop-down menu based upon a list found
in a different workbook. I can easily Define a Name by refering to the
named range in the other workbook.

MainWorkbook
Validation => List
List => 'SourceWorkbook.xls'!Source_List

SourceWorkbook
Source_List => A1:A20

However, I don't want to have to have SourceWorkbook open. The only way I
can think to get around it is to mirror Source_List in MainWorkbook as
"Slave_List" and refer to it directly. However, Source_List changes in
size.

Is there a worksheet function that would allow me to dynamically reflect the
content of Source_Range in the SourceWorkbook and paste it to MainWorkbook
under the name Mirror_Range to keep up with fluctuations in size?

2) Also on the topic of Validation using a Named list, my Named list might
contain the same data more than once. I would like the Validation List to
contain any given datum only once. Is there a worksheet function that would
remove repeated items that I could enter into the Validation List Source
line?

Bill
 
You could use MS Query (Data>Import External Data>New Database Query) to
import the Master List from the other workbook.

The imported range should be automatically named, or you can use a
dynamic formula to name the range.

There's a sample workbook here that uses formulas to extract unique
items from a list:

http://www.contextures.com/excelfiles.html#Function
under the heading 'Extract List of Unique Items'
 
Back
Top