Results 1 to 4 of 4

Thread: Data Validation List With Offset Formula Allows Free Text Entry

  1. #1
    Board Regular MartinS's Avatar
    Join Date
    Jun 2003
    Location
    Surrey, UK
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data Validation List With Offset Formula Allows Free Text Entry

    Hi
    I've searched and found several posts with users having the same issue, and I've checked my formula returns the correct range, but setting up a validation with the following formula allows free text, even though the option to stop if invalid entries made is ticked.
    To replicate this issue, follow these steps:
    Open a blank workbook and in Sheet1, A1, type 'Name';
    In cells A2, A3 and A4, enter 'Person A', 'Person B' and 'Person C';
    Define a range name 'NameList' to cover A2:A6, i.e. 2 rows past the last entry;
    In cell B2, create a Data Validation List, with the following formula:
    =OFFSET($A$2,0,0,COUNTIF(NameList,"*?*"),1)
    The default is that 'Show error alert...' is ticked, so this should stop users from entering text not found in the list, ie. Person A, B or C.
    Type Steven into B2, and note that it doesn't trigger the validation to fire and alert the user.
    I'm guessing this is to do with the formulae, but is there any way to fix this so that it lets the error handler work as expected?
    Many thanks
    Martin

  2. #2
    Board Regular MartinS's Avatar
    Join Date
    Jun 2003
    Location
    Surrey, UK
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation List With Offset Formula Allows Free Text Entry

    Actually, I fixed it myself to switching the offset formula to be used by the range name, therefore reducing the list based on the items in it, and then simply refer to the range name as the source. This then stops the user from entering free text as expected.
    Thanks
    Martin

  3. #3
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation List With Offset Formula Allows Free Text Entry

    I am intrigued - as I use this forum, I see many uses of the OFFSET function to produce dynamic lists, as presumably you are doing here. But why not just use Tables? You cannot directly use a Table reference as a Data Validation source, but if you select and name the column data as an "ordinary" range name, DV will then understand the range name - and the range name understabds about the Table. Just a thought, and obviously you do need Excel 2007 or above.

    Helping you to Excel

  4. #4
    Board Regular MartinS's Avatar
    Join Date
    Jun 2003
    Location
    Surrey, UK
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation List With Offset Formula Allows Free Text Entry

    Quote Originally Posted by ClaireS View Post
    I am intrigued - as I use this forum, I see many uses of the OFFSET function to produce dynamic lists, as presumably you are doing here. But why not just use Tables? You cannot directly use a Table reference as a Data Validation source, but if you select and name the column data as an "ordinary" range name, DV will then understand the range name - and the range name understabds about the Table. Just a thought, and obviously you do need Excel 2007 or above.
    in the list
    Hi Claire.
    Yes, fair point but it's a template that is in use by a lot of people I have a need to show two lists from this source, one including and the other excluding the first record, so sticking with dynamic ranges is IMO the best option.
    Thanks
    Martin

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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