+ Reply to Thread
Results 1 to 5 of 5

Data Validation - How to restrict the selection of the same value from the named list

  1. #1
    Registered User
    Join Date
    02-12-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Data Validation - How to restrict the selection of the same value from the named list

    Data Validation - How to restrict the selection of the same value from the named list

    I have create a named list 'Catagory' in sheet1. Now I want to use this list in sheet2 in such a way that restrict the selection of the values already selected from the list.

    Or (Allow selection of any value only once)


    Please check the attached XL sheet.

    Ques.xlsx

    Thanks in advance..!

    Karan

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Data Validation - How to restrict the selection of the same value from the named

    May I suggest to use helper column to display sorted list.
    See attachment.
    Attached Files Attached Files
    Quang PT

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Data Validation - How to restrict the selection of the same value from the named

    In addition to the above, you can avoid the #Num! errors appearing in the dropdown by creating a range name (MyRange) with the reference

    =Sheet2!$C$2:INDEX(Sheet2!$C:$C,MATCH("zzzz",Sheet2!$C:$C,1))

    Then use as the list source

    =MyRange

    See attached.

    cheers,
    Attached Files Attached Files

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Data Validation - How to restrict the selection of the same value from the named

    I prefer to avoid the use of array formulas when possible.


    Sheet1, B2: =IF(ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$8,0)),"",ROW()) , copied down the column as needed
    Sheet1, D2: =IFERROR(INDEX($A$2:$A$8,MATCH(SMALL($B$2:$B$8,ROW(A1)),$B$2:$B$8,0)),""), copied down column

    Create the dynamic named range for the validation list based on column-D, using this formula

    =OFFSET(Sheet1!$D$1,1,0,COUNTIF(Sheet1!$D$2:$D$8,"*")-1,1)
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  5. #5
    Registered User
    Join Date
    02-12-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Data Validation - How to restrict the selection of the same value from the named

    Thanks bebo021999, teylyn , Palmetto all three options solved my problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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