validation lists

gumby · Apr 2nd, 2002, 03:10 PM

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.

lenze · Apr 2nd, 2002, 03:18 PM

Use VLOOKUP to display the price based on the product choice.

gumby · Apr 2nd, 2002, 03:50 PM

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.

lenze · Apr 2nd, 2002, 03:56 PM

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

Cosmos75 · Apr 2nd, 2002, 04:03 PM

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.

Chris Davison · Apr 3rd, 2002, 10:38 AM

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.

Cosmos75 · Apr 3rd, 2002, 03:29 PM

Chris Davison,

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

CLW · Jun 22nd, 2004, 04:07 AM

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..

Aladin Akyurek · Jun 22nd, 2004, 06:02 AM

Quote:

Originally Posted by Cosmos75

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.

Apr 3rd, 2002, 10:38 AM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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. __________________ :: Pharma Z - Family drugstore ::

Jun 22nd, 2004, 04:07 AM	#8
CLW New Member Join Date: Jun 2004 Posts: 2	Re: validation lists 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..

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 03:10 PM	#1
gumby New Member Join Date: Apr 2002 Posts: 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.

Apr 2nd, 2002, 03:18 PM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Use VLOOKUP to display the price based on the product choice.

Apr 2nd, 2002, 03:50 PM	#3
gumby New Member Join Date: Apr 2002 Posts: 13	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.

Apr 2nd, 2002, 03:56 PM	#4
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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

Apr 2nd, 2002, 04:03 PM	#5
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	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.

Apr 3rd, 2002, 03:29 PM	#7
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	Chris Davison, Thanks for the correction, I totally forgot about that! Am glad somewhat caught that!!