Vlookup and Listboxes
Posted by Benny Carwyle on September 27, 2000 2:35 PM
I am new to using Excel and VBA and need to know how to create a drop down box that will allow you to select data from the list (also how to create the list) and I also would like to know if there is a good site for info on using vlookup in formulas?
Thanks
Posted by Ben on September 28, 2000 11:15 AM
Re: Listboxes
There are two kinds of listboxes--those from the forms menu and those from the forms menu and those from the control toolbox. I've only used the ones from the forms menu and think that they're more trouble than they're worth. It's a pain to move and resize them, you have to define the input ranges separately for each one that you have (barring a sophisticated VBA solution), and they return integers rather than the values they display.
So, I would only recommend using them if Data Validation > List doesn't suit your needs. Odds are that it will. With Data Validation > List, you can select a range of cells and assign lists of possible values to every cell in that range at once. And when a user selects something off of that list, it'll actually return that value to the cell, unlike a listbox.
The only real drawback is that your list can't be on another worksheet or in another workbook. You can work around that though by creating links to external lists in local cells. Any you can hide those cells if you don't want your users to see them.
As for VLOOKUP, Excel's help file should tell you almost everything you need to know about it.
-Ben
Posted by Celia on September 28, 2000 5:19 PM
Re: Listboxes
It is possible for the list used in Data Validation to be on other workbooks/worksheets. The following is an extract from on-line Help :-
"If the list of valid entries is in another worksheet or workbook, define a name for the external data on the active worksheet. In the Source box, you can then refer to the name on the same worksheet. For example, if the list of valid entries is in cells A6:A12 on the first worksheet in a workbook named Budget.xls, you could define the name ValidData on the active worksheet to be =[Budget.xls]Sheet1!$A$6:$A$12 and then enter =ValidData in the Source box."
Celia
