Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Create Data Validation List based on unique entries in column

  1. #1
    Join Date
    17th January 2005
    Posts
    47

    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!!??

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,846

    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. #3
    Join Date
    17th January 2005
    Posts
    47

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd July 2003
    Location
    Perth
    Posts
    71

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th January 2005
    Posts
    47

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th January 2005
    Posts
    47

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    2nd July 2003
    Location
    Perth
    Posts
    71

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    17th January 2005
    Posts
    47

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,305

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Create Unique List From Column With Duplicates
    By gsandy in forum EXCEL HELP
    Replies: 8
    Last Post: May 28th, 2012, 17:28
  2. Return Unique Entries In Validation List
    By jeffo701 in forum EXCEL HELP
    Replies: 1
    Last Post: June 26th, 2008, 01:08
  3. Create Column Of Data Based On Unique Items
    By gerardf in forum EXCEL HELP
    Replies: 3
    Last Post: December 19th, 2006, 12:09
  4. Replies: 2
    Last Post: October 5th, 2005, 16:59
  5. Replies: 1
    Last Post: July 3rd, 2005, 02:23

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
  •  
porno