Hi - I'm needing to validate data entry against values that are already in a separate worksheet, but need help with case sensitivity.
The use is to validate land parcel agricultural field names (AFN's) being entered are: 1 - actual existing field names (that the name actually exists) and 2 - That the text part of the name is entered in upper case.
The format of the name is an abbreviated Parish name followed by a number. An example could be in the Parish of St Peter with the AFN number 210, the name would be "P210".
Where it gets a little more complicated is that there are 12 Parishes and 2 of them start with the same letter St Mary and St Martin. Historically the answer to this has been for these 2 Parishes to use a 2 letter prefix in their names eg. St Mary number 333 would become "MY333" and St Martin 123 would be "MN123"
Additionally there are some AFN's that have been divided into smaller land units that have an added letter after the name, so for the above example "MY333", there could be a "MY333A" or "MY333B". There are also a very small number of anomalies with names like "T1508 (MN79)" which is the result of 2 land parcels in adjoining Parishes being amalgamated - Trinity 1508 & St Martin 79. This is the outfall legacy of hundreds of years of land ownership and inheritance on the small island of Jersey in the Channel Islands between England and France. In total there are 10,429 different names.
My plan so far is to have a primary worksheet with 1 column used to add or paste AGN's into with validation to ensure the correct name had been entered. The validation would be to compare against the list of 10,429 names held in a second worksheet. I have it up and running except for the case sensitivity. "p210" currently passes validation but it needs to fail and it should be "P210"
Assistance greatly appreciated
Spreadsheet attached
David :)
PC, Excel for Microsoft 365 MSO (16.0.13712.21210) 32 bit
