+ Reply to Thread
Results 1 to 5 of 5

data validation list to automatically adjust to changing list

  1. #1
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    2010, 2016, 365
    Posts
    328

    data validation list to automatically adjust to changing list

    Hi Folk..

    I have a constantly changing list of names in colum I. I use 'data validation' in cell c3 to show the list. How can I get the drop down List in cell C3 to only show the list of names and not all the blanks below it


    (I have been trying to create a 'Dynamic Name Range' using office help to no avail,, am I on the right lines for this)

    many thanks

    Jim
    Last edited by JamesT1; 05-17-2011 at 06:05 PM.

  2. #2
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: data validation list to automatically adjust to changing list

    What is the source of list in C3?

    EDIT: Oh I saw it (column I). So probably something like this in the validation source line:
    =OFFSET(I:I,0,0,COUNTIF(I:I,"<>"""),1)
    Last edited by ron2k_1; 05-16-2011 at 05:53 PM.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  3. #3
    Registered User
    Join Date
    05-15-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2007, Excel MAC 2008
    Posts
    63

    Re: data validation list to automatically adjust to changing list

    Jim

    you can convert your list of names in i to a table or list, do this in pre excel 2007 by CTRL+L and in excel 2007 onwards CTRL+T this makes the list Dymanic, you can then reference your list in your data validation which will automatically update when you add in new values to your list

    here is a exmaple.

    http://www.howtoexcelatexcel.com/?p=431
    and you can also set up your dynamic list on another sheet
    http://www.howtoexcelatexcel.com/?p=1698

    hope this helps

    Barbara

    www.howtoexcelatexcel.com

  4. #4
    Forum Contributor
    Join Date
    08-04-2008
    Location
    West Calder, Scotland
    MS-Off Ver
    2010, 2016, 365
    Posts
    328

    Re: data validation list to automatically adjust to changing list

    many thanks for your help guys...

    Ron,, The list in column 'I' had cells with equation in them which produced empty cells which then showed in the drop down list, my appologies I didn't know it did this,, (I do now). your hlp set me on the road to discovering the equation in the validation source line..

    =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

    many thank this helped me solve my problem

    Barbara

    you sites look very good,,, I will be using them in the future ,, many thanks for the tip about Ctrl+L to create the dynamic range
    Last edited by JamesT1; 05-17-2011 at 06:08 PM.

  5. #5
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: data validation list to automatically adjust to changing list

    We are here to help...

    Don't forget to mark your thread solved if we've cleared your doubts.

    EDIT: Yehhh! It's solved!

+ 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