Multi connection between deferent sources combo box

[HOGA36]

Dears:
This is my first post and I am new here, So please be patient with my request as I don't have well experience with VB as i am starter.

My Problem

What I Have ...
Worksheet with the following data
Colum 1: Brand Name ( A,B,C,D,......)
Colum 2: Generic Name (a,b,c,d,......)
Colum 3: Price (1,2,3,4,....)
Colum 4: Item Code: (A1,A2,A3,A4,.....)
-----------------------------

What I want to do:
1- Creating combo box for each colum
2- If I select any data from any combo box, the other combo boxs generate the related date to the selected item

------------

Regarding creating a combo box, i have a good background and I succeded in doing that. So the main problem with me now is how to linking the other combo boxs to auto generate the related data for each selected item. For Example


If I choose the Brand name, so the other related data of its generic name, price, and item code, automaticaly generated.




Thank you for your patience and prompt response.

---
Yours

[HOGA36]

Any Help Please >>>>> I ATTACHED HERE THE FILE

---
THANKS FOR YOUR HELP

[jeffreybrown]

IMO, it would be much easier to work with data validation for what you want to achieve here.

Just for futher clarifiation, AL.OH+MG+OXETHAZAINE SUSP for instance has one Brand name, one Price, and one Code. Do you really need this in a box or can it just be returned with a formula?

[HOGA36]

Dear Jeffreybrown:

Thanks for your response.

but actually I need this in a box, not with formula. thanks if you could help me in doing this as I am not expert in VBA
---

[jeffreybrown]

Here is your file using data validation instead of comboboxes.

[HOGA36]

Here is your file using data validation instead of comboboxes.

Dear Jeffrey:

Actully I need it in combobox form, and in same time there is no auto fill of the suggested solution for the other columns.

Thanks for your effert and your attention and looking for your answer with combo box option

[jeffreybrown]

See if this works for you

[HOGA36]

See if this works for you

Dear Jeffreybrown

You are the one (Matrix)......

Please would you provide me by the technique ..

Thanks alllllllloooooooooooooooooooooooooooooooooooooooootttttttt

[jeffreybrown]

You are very welcome.

Take a look in the name manager.

used the Offset function to create dynamic named ranges and then used those named ranges in the comboboxes.

One small macro on the change event of combobox1 to empty combobox 2, 3, and 4 on change of combobox1

[jeffreybrown]

The method used here is the OFFSET function which has five arguments. =OFFSET(reference,rows,cols,height,width)

Reference >> Uses O1 as the base (starting point)

Rows >> How many rows down to go. In this case, D6 is used to perform a match against column P to see how far down the column to find the Generic name.

Please Login or Register  to view this content.

Cols >> How many columns either to the left or to the right to go. In this case, since the start is in O1 and there is nothing to the left, we will go right. For BRAND you don’t go over any columns so nothing , but in the case of PRICE, 2 columns to the right and CODE, 3 columns to the right.

Height >> How many rows match D6?

Please Login or Register  to view this content.

Width >> Skipped

When all is put together

Please Login or Register  to view this content.

Notice the -1 at the end of the match which is due to the header row. If for example you pick AMBROXOL 30MG TABS, it is on the 23rd row, but if you go down 23 from O1 this puts you at 24 so we subtract 1 to get back to 23.

[HOGA36]

The method used here is the OFFSET function which has five arguments. =OFFSET(reference,rows,cols,height,width)

Reference >> Uses O1 as the base (starting point)

Rows >> How many rows down to go. In this case, D6 is used to perform a match against column P to see how far down the column to find the Generic name.

Please Login or Register  to view this content.

Cols >> How many columns either to the left or to the right to go. In this case, since the start is in O1 and there is nothing to the left, we will go right. For BRAND you don’t go over any columns so nothing , but in the case of PRICE, 2 columns to the right and CODE, 3 columns to the right.

Height >> How many rows match D6?

Please Login or Register  to view this content.

Width >> Skipped

When all is put together

Please Login or Register  to view this content.

Notice the -1 at the end of the match which is due to the header row. If for example you pick AMBROXOL 30MG TABS, it is on the 23rd row, but if you go down 23 from O1 this puts you at 24 so we subtract 1 to get back to 23.

Many Thanks Jeff.

You were too patient with my request, Thanks for giving me your time, and solving my problem

[jeffreybrown]

Glad it helped.

If you would, please do not quote entire post in your response. This just tends to clutter the board, but if the quote is pertinent to your follow-up questions, but all means, use it.