Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation (2003)

    I am trying to add a cell validation where the user can type in a number in one cell but if the number doesn't exist in another column, the entry would be invalid. Is this possible? I tried using the custom setting and an "exact" function but it didn't work.

    I would appreciate the help.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    GoodToGo,
    Set data validation of the cell to List and highlight the numbers that are valid.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    If I do it that way, the number cannot be typed into the cell and they would have to choose from the drop down list. Is there not another way?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation (2003)

    Gee - it works for me; the values in the list are available either through the drop-down or when I type them in.

    You can define the validation list either by XL renge references ($A$1:$A$15 style) or as a defined range name. The attached file has both. If you may be adding items to (or deleting - but then you get into 'orphan' problems) a defined name using a dynamic range name will work well.

  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 (2003)

    Assume the cell to validate is A1 and the values to lookup are in C1:C10. Then you can use this custom validation:

    <pre>=ISNUMBER(MATCH(A1,$C1:$C10,0))</pre>


    Steve

  6. #6
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    Thanks to all, it does work this way. I believe the problem was the entry field was set as text and it seems to be working now.

    Thanks again to all!

  7. #7
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    I have just discovered a problem with my cell validation. In the entry cell, if you type a number such as 01 and this number is in the validation list, it still returns an error message of an invalid entry. This happens wheather or not a zero is put in front of the number. I have checked the formatting of the cells to validate and they were both set the same, text, number, general. Entering any other number from 10 onwards is not a problem, why then will it not accept an entry less than 10?

  8. #8
    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 (2003)

    If your list has a a text value of "01" and not the number 1 then you must enter a text value for validation:
    Enter the single quote(') followed by the "01"

    Does the lookup have text or numbers in it or a combination of both?

    It may be easier to keep your "lookup list" all numbers (you can format cells, custom, 00 to display as "01" and still keep it a number), so that the user does not have to enter text.

    Steve

  9. #9
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    I can't seem to get them to enter regarless of the cell formatting (number, text, custom etc). I am attaching a sample of the spread sheet and it is column C that I am trying to validate. As you can see, these are codes not numbers but definately, entering as numbers is so much easier.

    Thanks

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    The first 9 values in column G were text and the rest were numeric. This will cause all kinds of problems if you use these with data validation. Does the attached do what you want?
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    Yes it does do what I want, however when I tried to do the same thing on my actual spreadsheet, no matter how I formatted either coloumn, it still would not work! Finally, I simply retyped the code list with a custom setting and it is now working great.

    Thanks for all your help!!!!!!!!

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    Its too late now, but you could have done what I did. Select the cells containing the text values, make sure that the cell format is "General", and then select "Text to columns..." from the data menu and click on the finish button in the dialog box.
    Legare Coleman

  13. #13
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003)

    Thanks, that will certainly be something to bear in mind for next time.

Posting Permissions

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