Having one drop down cell to show two columns of data

[PEAKCAB]

Help please

I have a invoice prepared and i am looking to have a part number from a drop down list but then want show in another column the description generated by the part number.

Any help would be greatly appreciated

Regards,

 

[Yongle]

What is the name of the sheet containing the parts list
Which column in that sheet contains the part number ?
Which column in that sheet contains the part's description ?

 

[PEAKCAB]

What is the name of the sheet containing the parts list
Which column in that sheet contains the part number ?
Which column in that sheet contains the part's description ?

Thanks Yongle,

the sheet name is Product
column A contains part number
column B contains description

Regards,

Rich

 

[Yongle]

1. Create Named Range with :
Name PartNumbers
RefersTo =OFFSET(Product!$A$2,0,0,COUNTA(Product!$A:$A)-1,1)

2. Add data validation in A2 with :
Allow List
Source =PartNumbers

3. Formula in B2 (copied down)
=VLOOKUP(A2,Product!A:B,2,0)

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Part No[/td][td]Description[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Part001[/td][td]Apple[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Part002[/td][td]Orange[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Part003[/td][td]Pear[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Part004[/td][td]Banana[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Part005[/td][td]Peach[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Part006[/td][td]Grape[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Part007[/td][td]Apricot[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Part008[/td][td]Melon[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Product[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td] FORMULA in B2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Part006[/td][td=bgcolor:#FFFF00]Grape[/td][td] =VLOOKUP(A2,Product!A:B,2,0)[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Part007[/td][td]Apricot[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Part008[/td][td]Melon[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Invoice[/td][/tr][/table]

 

[PEAKCAB]

Hello Yongle,

thanks for the information. I have tried to do this and I have #N/A in column B on the invoice sheet where the part number should be. Any ideas as to what i have done wrong? the part number column works fine.

Rich

 

[Yongle]

#N/A is when Excel is not finding the value being searched for
- Excel is not finding the part number in sheet "Product" which makes no sense whatsover
- the exact same list is used for data validation and for the search

- the value in A2 in the Invoice sheet comes from this list
OFFSET(Product!$A$2,0,0,COUNTA(Product!$A:$A)-1,1)
(which is A2 to the last cell with value in A in sheet "Product")

- Excel is looking for that value in the same sheet and column as generated the list (ie column A in "Products")
VLOOKUP(A2,Product!A:B,2,0) - column A


Which version of Excel are you using?

Post the part number Excel is not finding

 

[PEAKCAB]

Hello Yongle,

I am using Excel 2013. All the part numbers appear on the drop-down list - this works fine. It is when i put in the vlookup that i have the #N/A. This is what i am doing- so please feel free to point out any of my mistakes

create name range "PartNumbers" refering to formula you gave. Add data validation on A2 (invoice worksheet) - list - PartNumbers
this all works fine.

I then go to put the vlookup in column B on the invoice worksheet (cell B27) and the #NA comes up

Any ideas

Best regards,

Rich

 

[Yongle]

If the Part number is selected from dropdown in cell A27, then the formula in B27 should be

=VLOOKUP(A27,Product!A:B,2,0)

 

[PEAKCAB]

If the Part number is selected from dropdown in cell A27, then the formula in B27 should be

=VLOOKUP(A27,Product!A:B,2,0)

Absolute Genius! Thank you so much indeed

just one last question! The cells would have borders down the vertical sides is there a way of keeping these as when the cell is populated the border disappears

Regards,

Rich

 

[Yongle]

Why does the border disappear ? Is it due to conditional formatting ?
- check any conditional formatting rules pertaining to that range and amend accordingly