ListBox external source

[Guest]

In the first sheet of my file I have a cell (C3) that accepts input (a
Supplier code) to be validated against an external Excel file table/range
(Column A). Then C4 displays the Supplier name (Column B). I would like to
have this done by a ListBox in order to guide the user to all available
choices.

Please help me set it up since I have never used User forms (???) and such
Control objects in VBA and I have trouble initializing them. I found in the
Help system sample code using a user form but I don't use User Forms, not to
my knowledge anyway. Please bear in mind that the table is external.

Thanks in advance

 

[Tom Ogilvy]

Assume your table/range is in SupplierList.xls on sheet Data, Range:
"A1:B100"

In your sheet,
Select C3 then do

Data=>Validation.

You would need to use the List option and set it to

=List1

but do this after you create these names:

after you create a defined name
Insert=>Name=>Define
Name: List1
RefersTo: =[SupplierList.xls]Data!$A$1:$A$100

and another
Insert=>Name=>Define
Name: = List2
Refersto: =[SupplierList.xls]Data!$A$1:$B$100

Then in C4 you would have

=if(C3="","",Vlookup(C3,List2,2,False))

SupplierList.xls would need to be open when you use the workbook with these
formulas (at least for the Data=>validation to work).

 

[goober]

To set up a list for users to select from, on the menu bar, click on
"Data". Then "Validation". Under the settings tab in the "Allow"
window select "List". Under "Source" enter the range of data you want
to be in the list. This will create a pull down menu when the user
activates that cell.

P.S. The list of items needs to be in the same sheet.

Hopefully this will help you.

 

[Guest]

Hello Tom,
1. I have the same problem, except that on the name of the xls file there
are spaces 'like "this excel sheet.xls". And this seems to be refused by
Excel. How to solve this ?
2. Can we imagine (in term of performance), having a list of 6000 items
coming from another workbook ?

Thanks for your valuable help.

Francois.

Assume your table/range is in SupplierList.xls on sheet Data, Range:
"A1:B100"

In your sheet,
Select C3 then do

Data=>Validation.

You would need to use the List option and set it to

=List1

but do this after you create these names:

after you create a defined name
Insert=>Name=>Define
Name: List1
RefersTo: =[SupplierList.xls]Data!$A$1:$A$100

and another
Insert=>Name=>Define
Name: = List2
Refersto: =[SupplierList.xls]Data!$A$1:$B$100

Then in C4 you would have

=if(C3="","",Vlookup(C3,List2,2,False))

SupplierList.xls would need to be open when you use the workbook with these
formulas (at least for the Data=>validation to work).

--
Regards,
Tom Ogilvy

In the first sheet of my file I have a cell (C3) that accepts input (a
Supplier code) to be validated against an external Excel file table/range
(Column A). Then C4 displays the Supplier name (Column B). I would like to
have this done by a ListBox in order to guide the user to all available
choices.

Please help me set it up since I have never used User forms (???) and such
Control objects in VBA and I have trouble initializing them. I found in the
Help system sample code using a user form but I don't use User Forms, not to
my knowledge anyway. Please bear in mind that the table is external.

Thanks in advance

 

[Guest]

what if supplierlist.xls is not open? is there a way to access the data
validation drop down list without opening supplierlist.xls?or any way to
automatically access them in background?

Assume your table/range is in SupplierList.xls on sheet Data, Range:
"A1:B100"

In your sheet,
Select C3 then do

Data=>Validation.

You would need to use the List option and set it to

=List1

but do this after you create these names:

after you create a defined name
Insert=>Name=>Define
Name: List1
RefersTo: =[SupplierList.xls]Data!$A$1:$A$100

and another
Insert=>Name=>Define
Name: = List2
Refersto: =[SupplierList.xls]Data!$A$1:$B$100

Then in C4 you would have

=if(C3="","",Vlookup(C3,List2,2,False))

SupplierList.xls would need to be open when you use the workbook with these
formulas (at least for the Data=>validation to work).

--
Regards,
Tom Ogilvy

In the first sheet of my file I have a cell (C3) that accepts input (a
Supplier code) to be validated against an external Excel file table/range
(Column A). Then C4 displays the Supplier name (Column B). I would like to
have this done by a ListBox in order to guide the user to all available
choices.

Please help me set it up since I have never used User forms (???) and such
Control objects in VBA and I have trouble initializing them. I found in the
Help system sample code using a user form but I don't use User Forms, not to
my knowledge anyway. Please bear in mind that the table is external.

Thanks in advance