Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation by Exclusion (Excel 2000)

    In using data validation, one can have a list. Only data from that list can be input. Is it possible to reserve this: if a value (a string, a word) is in the list, it cannot be input into a cell.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation by Exclusion (Excel 2000)

    It can be done with something like
    =ISNA(VLOOKUP(D2, A2:A5,1,0)) where the validated (input) cell is D2 and the lsit of values to exclude is in A2:A5.

    Use data validation, custom and enter the formula as shown with the relevant range for your data.


    Hope it helps

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 Posts

    Re: Data Validation by Exclusion (Excel 2000)

    I don't think you can do it with a list, if you list of "can Not use" is in the range c1:c10 (change as appropriate) you can choose custom - formula
    =ISNA(MATCH(A1,C1:C10,FALSE))

    and you will not be able to enter anything from this list. You probably should add the list of items in the "error box" to let people know what not to enter.

    Steve

Posting Permissions

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