Data Validation List With Offset Formula Allows Free Text Entry

MartinS

Active Member
Joined
Jun 17, 2003
Messages
479
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top