+ Reply to Thread
Results 1 to 4 of 4

Expanding data validation

  1. #1
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Expanding data validation

    Hi friends,
    I want to enter a value >0 and avoide duplicate value in the following validation formula.

    Please Login or Register  to view this content.
    Any help will be highly appreciated.

    Thanking you.
    Sincerely,

    mso3

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,869

    Re: Help request data validation

    Try this out

    =AND(COUNTIF($L$2:$L$5000,L2)=1,ISNUMBER(L2),L2>0)

    Your formula will restrict duplicate entries but not letters..

    However, this formula will restrict numbers < 1, along with restriction towards entering any letters and duplicates..

    Hope it helps!

    Deep
    Last edited by NeedForExcel; 05-26-2015 at 12:35 AM. Reason: Had missed out on L2>0, JBeaucaire got me thinking the right way
    Cheers!
    Deep Dave

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help request data validation

    Maybe:


    =AND(L2>0,COUNTIF($L$2:$L$5000,L2)=1)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: Expanding data validation

    Solved. Thank you to both of you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Re: Data Validation - expanding old project
    By krisvadale in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-16-2015, 11:46 AM
  2. Replies: 5
    Last Post: 08-13-2011, 11:27 AM
  3. Expanding Data
    By Kypsis in forum Excel General
    Replies: 1
    Last Post: 03-04-2011, 05:20 PM
  4. Expanding data
    By Excels Cracking me up! in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2005, 10:10 AM
  5. Expanding Data validation from List
    By mark hansen in forum Excel General
    Replies: 2
    Last Post: 09-03-2005, 09:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1