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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

calacucciaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
erickrojasAuthor Commented:
Many thanks calacuccia
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Hardware

From novice to tech pro — start learning today.