Results 1 to 6 of 6

Thread: Data Validation

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

    I have data validation using the following formula - =OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C:$C),1)

    This works fine, however it appeas that there is no protection to prevent invalid data being entered into the cell as with normal data validation.

    Am I doing something wrong or can the offset formula be altered to prevent this.

    Any thoughts

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can only add data that is contained within the cells specified by your offset formula.
    Can you post a sample workbook that is exhibiting the issue?

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

    Thanks for your reply - please see the cut down version of the spread sheet showing the entry into a cell with a data validation list.

    Cheers
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Change the offset to not include the entire C column. Something like "=OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C$8:$C$1 00),1).
    or
    You can keep the Offset() as is but make sure the Ignore Blanks is not checked

    and

    Make sure the Error Alert Type is set to Stop and not Warning or Information. D79 are Stops, while D10 is set as a Warning will still allow for items not on the list to be accepted

  5. #5
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by verada View Post
    Lounger,

    I have data validation using the following formula - =OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C:$C),1)

    This works fine, however it appeas that there is no protection to prevent invalid data being entered into the cell as with normal data validation.

    Am I doing something wrong or can the offset formula be altered to prevent this.

    Any thoughts
    When setting up data validation you have the option of setting an error message when invalid data is entered. See attached picture.

    Regards,
    Maria
    Attached Images Attached Images
    Maria
    Simmo7
    Victoria, Australia

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

    Will give your suggestions a go

    Cheers

Posting Permissions

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