Solved: Creating a drop down list with source from another sheet

[nathan_nz]

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
[list] 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.

[anandbohra]

See this link


http://www.contextures.com/xlDataVal05.html

=filename!listname
e.g. =DataValWb.xls!CustName

[nathan_nz]

Hello Anand,

Thank you very much for your time.

Thats exactly what I wanted.
But it is mentioned that "the workbook which contains the list must be open", I want to use the list even if the source book is closed.
Cant I do that?

[anandbohra]

hi nathan_nz

I googled but come to know that the source should be opened
but if u dont want the source to be opened u should take help of VBA
which is better explained in below mentioned page


http://exceltip.com/st/Fill_a_ListBox-control_with_values_from_another_workbook_using_VBA_in_Microsoft_Excel/410.html