validation lists

gumby

New Member
Joined
Apr 1, 2002
Messages
13
I have a worksheet with validated cells one is products the other is prices. Each list has several entries. What I want to do is tie them together so that when I select a product the correct price also comes up.

thanks.

G.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I'm not familiar with vlookup. I looked at it and couldn't figure it out. Could you explain a bit more.

my sheet is as follows

cells b2 c2 d2
Products blank prices

thanks again.

G.
 
Upvote 0
Create a table somwhere on your sheet with two columns, One for product, one for the price. Say it's in $P$3:$Q$12. Now, if your product is in B2 and you want the price in D2, place this formula in D2

=VLOOKUP(B2,$P$3:$Q$12,2,FALSE)

This will return the price next to the product from your table.

See help file for examples
 
Upvote 0
For the previous example.

=VLOOKUP(B2,$P$3:$Q$12,2,FALSE)

$P$3:$Q$12 must be sorted by column P, and take note that column P is where the formula looks up B2.
 
Upvote 0
Hey Cosmos,

that last FALSE arguement forces VLOOKUP to find an exact match, if it doesn't find it, it returns #N/A : this means you don't need to sort your VLOOKUP range at all - as an exact match only will do

you only need to sort the range if you don't use FALSE as the last arguement (ie TRUE, 1 or ommitted). In these cases, if it can't find an exact match, it will bring back the closest (hence the need for sorting) as it just defaults to the next value

you can also use 1 and 0 instead of TRUE and FALSE if you want to save time and space.
 
Upvote 0
Chris Davison,

Thanks for the correction, I totally forgot about that! Am glad somewhat caught that!!
 
Upvote 0
Using the vlookup, how can I code in VBA code?

CostCenter = Application.VLookup(Range("RangeName").Value, _
Worksheets("sheet2").Range("Rlookup"), 2, False)
If Not IsError(CostCenter) Then
cell.Offset(0, 1).Value = CostCenter
End If

wage = Application.VLookup(Range("RangeName").Value, _
Worksheets("Sheet2").Range("Rlookup"), 3, False)
If Not IsError(wage) Then
cell.Offset(0, 1).Value = wage
End If


Above VBA code I had define the range for "RangeNAme","Rlookup"... Any mistake in the code? I can't execute it... It gave me error message! Any help is very appreciated..
:)
 
Upvote 0
Cosmos75 said:
Chris Davison,

Thanks for the correction, I totally forgot about that! Am glad somewhat caught that!!

With "must" you were in error. But, if the table can be sorted, the speed of linear search with VLOOKUP(...,FALSE) will improve.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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