Dropdown list multiple column

[john5599]

Hi please need a help.


I have a excel sheet as given below. I need to make a drop down list of that, when I select item name from the dropdown list , item code also come in the next column. Is it possible .

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Item Name[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]TRIM CLIP ON LED PANEL 6W 6500K[/TD]
[TD]LHEAABP7IL1W006[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]TRIM CLIP ON LED PANEL 12W 6500K[/TD]
[TD]LHEAABP7IL1W012[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]TRIM CLIP ON LED PANEL 18W 6500K[/TD]
[TD]LHEAABP7IL1W018[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="bgcolor: #92D050"]LED ADORE 10W B22 CDL 3STAR LAMP[/TD]
[TD]LHLDEUEEML8R010[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="bgcolor: #92D050"]LED ADORE 9W B22 CDL 3 STAR LAMP[/TD]
[TD]LHLDEUEEML8R009[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]TRIM CLIP ON PANEL 24W 6500K[/TD]
[TD]LHEAABP7IL1W024[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]TRIM CLIP ON LED PANEL 6 W 4000K[/TD]
[TD]LHEAABP6IL1W006[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

 

[kweaver]

Assuming you know how to create the drop-down list from, say, column A...
Let's say that's in D1. then, in E1, write:

=VLOOKUP(D1,A2:B8,2,FALSE)

Does that do it?

 

[Akuini]

How many Item Name do you have? If you have a lot, say more than 100, then instead of data validation I suggest you use a searchable combobox, so for example, if you type ‘lamp’ in the combobox then only items with ‘lamp’ will be shown.
As for filling the next column with the item code, it can be done using ‘Worksheet_Change’ event.
If you’re interested in this method, just let me know, I can create an example for you.
You can try the searchable combobox example (but without filling the next column part) in this thread:
https://www.mrexcel.com/forum/excel-questions/1089693-data-entry-force-selection.html

 

[john5599]

Sir, Item_Master is in sheet named Master and dropdown list need to be in Data2 Sheet column H(Item Name) column I(Item Code). so how VLOOKUP FOURMULA WILL CHANGE.

 

[john5599]

Good I am interested I am here attached link of my sample sheet, please do the needful.




https://www.dropbox.com/s/c9s57ebquxwpy1k/Book555.xlsx?dl=0

 

[john5599]

hello sir
can you please change the VLOOKUP as per attached file link below

https://www.dropbox.com/s/c9s57ebqux...k555.xlsx?dl=0

 

[Akuini]

Good I am interested I am here attached link of my sample sheet, please do the needful.

https://www.dropbox.com/s/c9s57ebquxwpy1k/Book555.xlsx?dl=0

Ok, here’s an example:
https://www.dropbox.com/s/2w9an0lriz53nfm/Book555 - searchable combobox.xlsm?dl=0

Note:

• The combobox will be used in range "H2:H20", you may change that in this part of the code:

'range where you want to use the combobox (blue area)
Private Const xCell As String = "H2:H20"

• I use vlookup formula in col I sheet Data2, you may change it to suit.

• To understand how the searchable combobox work you can read the instruction in the sample workbook :

https://www.dropbox.com/s/t1sbmp3lm...t to cell, search as type - example.xlsm?dl=0

 

[john5599]

Thank you for your great help. and your kind response

Can we hide #N/A in VLOOKUP field

 

[Akuini]

Thank you for your great help. and your kind response

Can we hide #N/A in VLOOKUP field

You can replace the formula with this:

=IFERROR(VLOOKUP(H2,Master!A$2:B$10000,2,FALSE),"")

 

[john5599]

Thanks