Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: validation lists

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris Davison,

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

  8. #8
    New Member
    Join Date
    Jun 2004
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,649
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •