Data Validation List With Offset Formula Allows Free Text Entry

[MartinS]

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

 

[MartinS]

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

 

[ClaireS]

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.

 

[MartinS]

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