Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Data validation (XP)

    I have this worksheet with 3 columns... name(a) address ([img]/forums/images/smilies/cool.gif[/img] and townę.. with some information in it. I tried with data validation to get this information onto another worksheet.
    I have many empty rows to people can add the information later
    I typed in List and =Clients.. I called the range Clients...
    But it gives me an error message.
    I want the columns A1, b1 and c1 to move automatically into fields A1, A2 and A3 with a dropdown list

    It all looks easy and I have checked earlier questions and they told me to name the range, so why won't it work ???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data validation (XP)

    Data validation only works on one cell at a time, and it can only use a list consisting of one column (or row).
    If the names in column A of the list worksheet are unique, you can name this column Clients, and use that in data validation on the other worksheet. You can then use VLOOKUP formulas to retrieve the address and town.
    If the names are not unique, and the user needs to select a name/address/town combination, you cannot do that with data validation. You must use a combo box from the Control Toolbox toolbar. Set its Column Count property to 3, its LinkedCell property to SheetName!A1 where SheetName is the name of the sheet where you want to have the result, and its ListFillRange property to Clients. You'd need code to populate the other cells.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    Bummer, I'd hoped it be easier then that. Attached is a cut down version of the spreadsheet. When I fill out the properties of the combo box only the column count property stays. The rest disappears.
    At the moment there are only 2 names and addresses int he list. I have set the range to 999 as it will be populated by the user.

    Never done this before. Thanks for your help

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    And this time with spreadsheet

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    See if this works.

    Take a look at the properties of the combobox, LinkedCell and ListFillRange.
    The vlookup formulas are below the result of the selected company name.

    Regards,
    Joop

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    HI there,
    Sorry for the late reply but time difference and all that.
    I'm almost there.. As you can see from the attached workbook the information shows in the correct cells. But.... in the cell where the company name is, is shows - company name - address - town and company name again. This is wrong and very confusing for my user. When I change the range in the "clients" sheet, the street address cell and the town cell give an error.

    I realise its something small. It usually is, but I've tried everything.

    I had to continue with the VLookup as I didn't really understand the combobox solution.

    I have coloured the cells in question, so it's obvious.

    Thanks soooo much

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    Hi,

    Your DataValidation range was wrong. You defined three colums as 1 to select from, which was the reason for the
    quote
    But.... in cell where etc.
    unquote
    I changed it in the attached workbook in the clients sheet.

    btw do you want the datavalidation for cell b15 in the item sheet?

    Success!
    Joop

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Data validation (XP)

    Thanks !!!!!!!!!!!!
    Works wonderful
    Thanks again

Posting Permissions

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