Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Data Validation - Extending List (2003)

    I have a named area called "positions", this is pasted into the data validation - list function. The problem I'm having is that I'm not sure how long the information contained in the "positions" will be, I've extended the range to include what are currently empty cells for future use & ticked ignore blanks in the list menu, however this makes the pull down very long with blank lines.

    Is there away that the list will grow only when the cells are filled?

    I hope that all make sense.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation - Extending List (2003)

    You can use a dynamic named range. Let's say that the list of items is on Sheet1 starting in A1, and that there are no other populated cells in column A.
    Select Insert | Name | Define...
    Enter a name, for example List.
    In the Refers to box, enter the following formula:

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

    Click Add, then Close.
    You can now use the defined name in validation: in the Source box, enter the formula

    =List

    This even works if the source list is on another sheet than the cell(s) for which you want to set validation.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Data Validation - Extending List (2003)

    Hans,

    Thankyou - works great - just what I needed

    Regards

Posting Permissions

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