Announcement

Collapse
No announcement yet.

Create Data Validation List based on unique entries in column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create Data Validation List based on unique entries in column

    This seems like a difficult one, way above me.

    What I want is a data validation list in a cell on one worksheet. The list should include all the unique values from a range in another worksheet (There will be more that one similar value in the range and but I don't want them to repeat in my list).

    Is this possible!!??

  • #2
    Re: Create Data Validation List based on unique entries in column

    FreddyKrueger,

    In order to use the standard data valadation method you'ld need to create a seperate list of those unique values.

    Or you could use an ActiveX object ( combobox ) and then loop through the list and pull only unique values out?


    Which do you want?
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: Create Data Validation List based on unique entries in column

      Think I'll create a list on the same worksheet containing the unique values, hide it and them just reference it from the validation box.

      My problem is still, how do I loop through a column and only extract a list of unique values (with no repititions)

      F

      Comment


      • #4
        Re: Create Data Validation List based on unique entries in column

        Hi FreddyKrueger,

        Here is one solution with the use of advanced filter. I have writeen a macro which filiters the unique records and then returns these to Column A on the source data sheet. The data valistion on the sheet select list is done via the use of a named range referanced back to coulmn A.

        Have a look it should achieve what you require.

        Regards

        Bevan
        Attached Files

        Comment


        • #5
          Re: Create Data Validation List based on unique entries in column

          So easy!. The procedure I wrote was so long.
          Thanx alot bvan

          Comment


          • #6
            Re: Create Data Validation List based on unique entries in column

            One more question
            The data validation reference to my column of unique values will include alot of blank values because I don't know how long my unique list will be. Is there any way to make the validation reference dynamic so that it shows no blanks.

            F

            Comment


            • #7
              Re: Create Data Validation List based on unique entries in column

              Hi FK,

              I have found a thread which achieves what you want to do, and built it into the attached spreadsheet.

              http://ozgrid.com/forum/showthread.p...ic+named+range

              The result is achieveing what you want so good luck.


              Bevan
              Attached Files

              Comment


              • #8
                Re: Create Data Validation List based on unique entries in column

                You da man bevan
                Would've taken me days to figure this one out!
                Thanx alot

                Comment


                • #9
                  Re: Create Data Validation List based on unique entries in column

                  Hi,

                  Try this simplified formula for Dynamic Range

                  =OFFSET('Source Data'!$A$1,0,0,MATCH(REPT("z",90),'Source Data'!$A:$A))

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Create Data Validation List based on unique entries in column

                    As mentioned earlier: Please don't revive old posts simply to promote your own site...

                    Comment


                    • #11
                      Re: Create Data Validation List based on unique entries in column

                      Hi All,
                      I am trying to insert a drop down list in excel but I have duplicate txt that I would like to show only once in the drop down. Is there a code I can put or a formula without inserting too many columns or pivot tables? The below is my sample and please note the selection is much larger. Thank you for your help in advance.

                      Master Category
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Commodity
                      Packaging
                      Packaging
                      Packaging
                      Packaging
                      Packaging

                      Comment

                      Working...
                      X