Excel's ComboBoxes

How can I create a combobox or a list box, with options from another worksheet and use those options to search for info onto another worksheet?
erickrojasAsked:
Who is Participating?
 
calacucciaConnect With a Mentor Commented:
Hi Erickrojas,

Suppisoing you work on 'Sheet 1', the options are on Sheet 2, Range A1 to A12, and your search for info will be in Sheet 3,
First right click on the menu bar and select the 'Forms' menu (a small window will be opened on your worksheet with a.o. the combobox), click on the combobox (or list box) and then drag with your mouse the zone where this box is to be placed on the actual worksheet.

The box is created, now select it and right-click, choose format control, go to tab 'Control'.

Put the cursor in the box 'Input Range' and go to the tab sheet2, and select the list of options (for the example, the formula filled in will be: Sheet2!$A$1:$A$12.

Put the cursor now in the box 'Cell Link' and select a cell on your current worksheet sheet1 (e.g. B5). The result is that after the selection in the combo box, cell B5 on sheet1, will contain a number cooresponding to the postion of your last selection in the options list (e.g. if you had selected the value in Sheet2!A5, you'll see a number 5 in cell B5 Sheet1).

Now, you can either use this number or convert this number to the actual text or value in the options list.

To do this, name the options list (go to sheet2!Range A1:A12 and select this range, on menu bar: Insert/Name/Define/ Type name "TestName" or anything you want in the name box, click OK button.
Now enter following formula in the cell B6 of sheet1:
=Index(TestName;$B$5)

Now the option you selected by the box will be shown in cell B6 of sheet1, and you can use this value to search on the third worksheet, sheet3. To select the cells of sheet3 in the search formula's of sheet1, just select them when inside the formula (go to specific sheet, and select the range), Excel will automatically give the correct cell references, which will always be of this type:

=Formule(Sheet3!$A$1:$B$5, "Anything", Other Argument)

Hope this helps,

if you need more help from here on, just ask.

Calacuccia
0
 
erickrojasAuthor Commented:
Many thanks calacuccia
0
All Courses

From novice to tech pro — start learning today.