Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: Data Validation - setting criteria

    Hello, I have a column on a spreadsheet that uses Data>Validation to select values from a list. The source for the list is a separate sheet called "Projectlist".

    I am able to set the Source to =ProjectList and it displays the values, however it does not display ALL the values. It only goes up to a certain point.

    Is there a way to specify the range of values when designating the source in the Validation criteria?

    Thanks for your help,
    Grace

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by palto1
    Hello, I have a column on a spreadsheet that uses Data>Validation to select values from a list. The source for the list is a separate sheet called "Projectlist".

    I am able to set the Source to =ProjectList and it displays the values, however it does not display ALL the values. It only goes up to a certain point.

    Is there a way to specify the range of values when designating the source in the Validation criteria?

    Thanks for your help,
    Grace
    Sounds like you need to name the ProjectList as a dynamic named ranged.

    Suppose that ProjectList is on Sheet1, Column B.

    Then go to Insert > Name > Define. In the resulting dialog box, you should see ProjectList already there. Click on it, so it is in the top Name box. Then in the Refers to: box at the bottom, type in this:

    =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

    Clcik the Add button. Then OK button. Now, the ProjectList will always refer to the entire list, even if you add more items to the list.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    68

    That worked

    THANK YOU! That was it. I can move on now!

Posting Permissions

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