Hi All,
Newbie, here with my 1st post.
I have data validation on a cell within a sheet that is passed to a lot of customers.
The validation ensures that the length of the data entered is less than 24 characters and then checks each of these characters to ensure they are valid characters.
There is then a Sum of these valid characters and if this equals the count of unique characters within the cell this passes, if not then at least one of the characters isn't valid.
The formula for this is below
=IF(AND(LEN(A2)>0,LEN(A2)<24),SUM($D$2:$D$156)=(COUNT(1/FREQUENCY(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:255"))))),FALSE)
The formula works as expected and had initially passed my testing, however the first person to test this after me encountered an error. If text is entered into the cell and then TAB or ENTER is pressed the valiidation works as expected, if the user keys in the text then clicks on the next cell the validation will return an error message.
This appears to not allow the cells within column D to calculate the correct values.
Does anyone know why this works for TAB or ENTER but not with a mouse click, and is there any way around this?
Hopefully this makes sense
Chris
AMENDMENT This happens across Excel 97, XP 2003, 2007 and 2010.
Bookmarks