Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation dropdown list (2003 SP2)

    Good afternoon

    My first ever voyage into dropdown data validation boxes is going badly

    I have 2 worksheets OrderForm and ProductList, on the ProductList worksheet I have named the Product range (A2:A101) as ProductLookup, on the OrderForm I have highlighted B5:B25 and used Data|Validation|List|=ProductLookUp, so far so good all of these cells now look up the ProductList.

    On the OrderForm I now want (in Column C) to have the price shown when a product is selected in one of the cells B5:B25 an have entered, IF(B5="","",VLOOKUP(B5,ProductLookup,2,FALSE)) but I am getting a #REF! error.

    In my ProductList worksheet I have all of the prices listed in B2:B101 to corrospond with the ProductLookup range and In have made sure that the cells are formatted for Currency as they are in the OrderForm C5:C25 cells.

    I cannot see what I am doing wrong, any help appreciated

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation dropdown list (2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 25-Oct-07 08:40. Added PS)</P>If ProductLookup is only A2:A101 it has no second column to be "Vlookuped". Try this formula:

    =IF(B5="","",OFFSET(ProductLookup,MATCH(B5,Product Lookup,FALSE)-1,1,1,1))

    The other option is to create a name with 2 columns to be used for the lookup

    Steve

    Another option is to change your named range to A2:B101 and use in datavalidation (instead of ProductLookup):
    =INDEX(ProductLookup,0,1)

    The "index" as written will convert the 2 cols to 1 col for the validation...

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation dropdown list (2003 SP2)

    Thanks Steve I will try your methods, as mentioned I have never used Data Validation before and I had already named the ProductLookup range as A1:B101 but when I tried to reference it in the OrderForm it said something about not being able to have 2 columns, that is why I opted for the 1, probably me though

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation dropdown list (2003 SP2)

    Hi Steve

    I thought I would have another go doing it the wway I first tried and how you said

    "If ProductLookup is only A2:A101 it has no second column to be "Vlookuped".

    So in the ProductList I selected A1:B101 and called it ProductList, on the OrderForm I select cells B5:B25 Data Validation - List - ProductList and got this reponse again (see below), I will now try your second option

    Thanks again

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation dropdown list (2003 SP2)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Reads the PS in <post:=672,771>post 672,771</post:>

    My original comment in the post was to make an ADDITIONAL name: a one column name for the validation and a second for the lookup. My PS shows how you can make the 2 column (or any column) named range and make it into a single column named range....

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation dropdown list (2003 SP2)

    Oops! sorry, I did'nt really realise that you were referring to that

    Ta

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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