Use item selected from Combo Box to retrieve data on another Worksheet

[karen999au]

I have an excel workbook consisting of 2 Worksheets. The first one contains a Combo Box which displays a list of product codes taken from a table in Worksheet 2. Worksheet 2 also shows in the table a description for the product codes along with other information relevant to each product code.
The table has the product codes in column 1 and the description in column 2 and other information in subsequent columns across the page. I have used the Combo Box from the Control Toolbox as I need to have autocomplete for this list of over 2,000 items. How do I retrieve the information from Worksheet 2 relating to the selected product code in the Combo Box and then display it on Worksheet 1 where the Combo Box is located ? Many thanks for any assistance you can provide.

 

[Fenrirshowl]

Can't you simply use an INDEX function and the result from the linked cell of the combobox (right click the combobox and then select Format Control) to give the required entry in the list?

Fen

 

[SkipVought]

Hi,

I don't like links to other workbooks.

I often use MS Query to pull data from other workbooks quite simply. faq68-5829.

You can populate a cell with the SelectedValue for the combobox and use that cell as the linked parameter value for the query.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[karen999au]

Thanks for your suggestions. My combo box was created using the Control Toolbox rather than the Forms Toolbar so the Linked Cell works differently and returns the same data as appears in my combo list and having a look at INDEX which I haven't used before, it seems to be useful for static data where my product list is constantly having items inserted throughout. Alas, I don't have MS Query available to me. Good news though I have solved the problem using the Linked Cell placed in the same row as the combo then converting that using MATCH to the row number where the required data is stored on the other Worksheet and then using VLOOKUP to retrieve the data and display it on the first Worksheet. Thanks for your time in any case. Regards

 

[SkipVought]

"My combo box was created using the Control Toolbox rather than the Forms Toolbar so the Linked Cell works differently and returns the same data as appears in my combo list and having a look at INDEX which I haven't used before."

All you need from your Combobox is the Selected VALUE, not the INDEX.

"Alas, I don't have MS Query available to me."

Really? It's a built-in feature that comes with Excel!

"Good news though I have solved the problem using the Linked Cell placed in the same row as the combo then converting that using MATCH to the row number where the required data is stored on the other Worksheet and then using VLOOKUP to retrieve the data and display it on the first Worksheet."

Well good for you for finding a solution. As I stated earlier, I don't like links to other workbooks as they present some anoying problems that can be avoided.



Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[karen999au]

Thanks for your comments Skip, I have two Worksheets in the same Workbook and not two separate workbooks. Regards