Using a combo box in excel 1

[JohnnyR]

I am using a combo box to pull a list from another sheet within the workbook. What I would like to do is have the rest of the data in that row follow with the choice that was made in the combo box. For example I pick the equipment type and the cost/hr, cycles/hr, floor space/req follow and fills in the row. How do I do this?

 

[joerd]

The combo box will provide you with the index of the item selected in the linked cell (how you specify that is dependent on which combo box you have used: the one from the control toolbox or the one from the forms toolbar)

So if you pick the first item in the list, the value of the linked cell becomes 1, etc.

Then, using the INDEX function, you can return any cell in the proper row. So if your list is on Sheet2!$A$1:$H$20, and the linked cell is on Sheet1!$A$3, the functions should read:

=INDEX(Sheet2!$A$1:$H$20, Sheet1!$A$3, 1)

to return the data in column A of Sheet 2,

=INDEX(Sheet2!$A$1:$H$20, Sheet1!$A$3, 2)

to return the data in column B of Sheet2, etc.


Regards,

Joerd

 

[sc]

I achieve this by using the combo box and the vlookup function together.

It is much the same as JOERDs response.

regards
sc

 

[trainguy]

Very useful post, everyone.

I had combo boxes with vlookups, but changed them to the more succinct INDEX function formulation. It saves you from having to input columns of numbers or labels to the left of your range, only for indexing purposes!

Free tip: To make changes to a group of worksheets in an excel file, before making the change in the active sheet, hit ctrl and click on all the sheet tabs you want to affect. After making whatever change (incl. setting up Headers/ Footers), right click on one of the affected sheet tabs and select Ungroup Sheets.