I have a worksheet (Excel 2010) where membership numbers are being entered as people attend a meeting. I want the input validation to test:
1) has the number already been entered - fail if it has
2) is the number contained in the full list of membership numbers on another worksheet in the same workbook -fail if it isn't
PLUS
If the validation fails I want to display a different error message for each failure.
I've worked out a custom validation formula for the validation tests:
=AND((COUNTIF($B$6:$B$1405,B6)=1),(COUNTIF('Setup Sheet'!$A$5:$A$104,B6)=1))
which seems to work ok, but I can't see how to get two different error messages. I don't want to use macros or VB as the finished spreadsheet will be used by other people, who may have them disabled.
Can anyone help?
Bookmarks