Creating a drop down list with source from another sheet

[NathanNZ]

Hi All,

I am new-bee here.
I am using MS EXCEL 2003.
To create a drop down list, I will go to [Data]->[Validation] and select

• from the Allow selection list. In Source field, I will input the source data for my list.
  Here lies my problem, How can I provide data in another workbook as Source to my drop down list?
  
  Thanks in advance.

 

[combo]

No need for vba.

If you use data from another sheet in the same workbook, name source range and apply named range as list source.

If the source is in external workbook, name the source data and use external data import: either import data or use database query. Imported data have the name as set for external data range, but it's local (sheet level). To extend it to global add global name: [GlobalName]=[SheetWithImportedData]![ExternalDataRangeName]. Now it can be used as in the first case.

If you plan to change location of the source file and you use two workbooks, it will be simpler either to try to combine data into one workbook or use vba to build the string for 'Formula1' argument. Turn on the vba recorder to see how it works.

combo