Having one drop down cell to show two columns of data

PEAKCAB

New Member
Joined
Jul 16, 2018
Messages
49
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,
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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 ?
 
Upvote 0
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
 
Upvote 0
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
A
B
C
1
Part NoDescription
2
Part001Apple
3
Part002Orange
4
Part003Pear
5
Part004Banana
6
Part005Peach
7
Part006Grape
8
Part007Apricot
9
Part008Melon
10
Sheet: Product

Excel 2016 (Windows) 32 bit
A
B
C
D
1
FORMULA in B2
2
Part006Grape =VLOOKUP(A2,Product!A:B,2,0)
3
Part007Apricot
4
Part008Melon
5
Sheet: Invoice
 
Upvote 0
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
 
Upvote 0
#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

 
Upvote 0
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
 
Upvote 0
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)

 
Last edited:
Upvote 0
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
 
Upvote 0
Why does the border disappear ? Is it due to conditional formatting ?
- check any conditional formatting rules pertaining to that range and amend accordingly
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top