Ordinance Survey Great Britain (OSGB) Data Validation formula puzzle!...

[Nobby_the_Mouse]

Dear Excel Wizards of the world...

I'm trying to make a Data Validation formula and have got very very stuck! The problem...

I need users of a spreadsheet to only be able to enter valid Ordinance Survey Great Britain (OSGB) grid references into a cell. These grid references will be only the following lengths:

4 figure (1km precision): e.g. SU1234
6 figure (100m precision): e.g. SU123456
8 figure (10m precision): e.g. SU12345678
10 figure (1m precision): e.g. SU1234567890

The validation needs to be able to allow two letters at the start, from the following look-up table only:

HP, HT, HU, HY, HZ, NA, NB, NC, ND, NF, NG, NH, NJ, NK, NL, NM, NN, NO, NP, NR, NS, NT, NU, NW, NX, NY, NZ, OV, SC, SD, SE, SG, SH, SJ, SK, SM, SN, SO, SP, SR, SS, ST, SU, SV, SW, SX, SY, SZ, TA, TF, TG, TL, TM, TQ, TR, TV

Also, the formula should allow the user (if possible) to enter a space after the two letters and at the mid-point of the digits, for example:

4 figure (1km precision): e.g. SU 12 34
6 figure (100m precision): e.g. SU 123 456
8 figure (10m precision): e.g. SU 1234 5678
10 figure (1m precision): e.g. SU 12345 67890

Any deviation from the above rules would mean the entry is invalid and an error alert is generated.

This is part of an important species data collection methodology for a wildlife charity, so your help will be much appreciated!

Cheers,
Ben

 

[mole999]

If you are doing it via a data entry sheet, then you can fix spaces internally, tied to a sheet change event on the desired range.

In essence, strip the space, check the length of the answer which has to be even between 6 & 12 characters, force to UPPER, then you can test to see if left(cell,2) is in the allowed lettering, if any of those fail you can throw a message box and reset

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Can the digits be anything, including with any number of leading zeros? eg "NN0203" or "SX000000"

2. If needed, would a solution involving vba be acceptable?

 

[mole999]

Peter > https://en.wikipedia.org/wiki/Ordnance_Survey last two images shows how UK mapping is laid out using the normal Grid

A leading ZERO will be present in some, the only exception really is 00 bottom left of last image on the full scale that could be quoted as SV0000000000, SW0000000000, though a real rarity

 

[Nobby_the_Mouse]

Welcome to the MrExcel board!

1. Can the digits be anything, including with any number of leading zeros? eg "NN0203" or "SX000000"

2. If needed, would a solution involving vba be acceptable?

Good questions! Yes, the number can include leading zeros exactly as you state in your examples. I'm afraid I've got limited knowledge of Excel (and no knowledge relating to VBA), so my preference would be to chuck a formula in the Data Validation if possible.

Cheers,
Ben

 

[Peter_SSs]

I can't see any feasible way to create a DV formula for that, noting that a custom DV formula has a limit of 255 characters.

You could try the following vba code. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm) & you will need to enable macros.

Test in a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range, Changed As Range, Failed As Range
  Dim RX As Object
  
  Set Changed = Intersect(Target, Range("A2:A10"))  '<- Set the DV range here
  Set Failed = Cells(Rows.Count, Columns.Count)
  If Not Changed Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Pattern = "^(|HP|HT|HU|HY|HZ|NA|NB|NC|ND|NF|NG|NH|NJ|NK|NL|NM|NN|NO|NP|NR|NS|NT|NU|NW|NX|NY|NZ|OV|" _
                & "SC|SD|SE|SG|SH|SJ|SK|SM|SN|SO|SP|SR|SS|ST|SU|SV|SW|SX|SY|SZ|TA|TF|TG|TL|TM|TQ|TR|TV|)" _
                & "(( ?\d{2} ?\d{2})|( ?\d{3} ?\d{3})|( ?\d{4} ?\d{4})|( ?\d{5} ?\d{5}))$"
    For Each c In Changed
      If Len(c.Text) > 0 Then
        If Not RX.test(c.Text) Then Set Failed = Union(Failed, c)
      End If
    Next c
    Set Failed = Intersect(Failed, Changed)
    If Not Failed Is Nothing Then
      Application.EnableEvents = False
      Failed.ClearContents
      Application.EnableEvents = True
      MsgBox "Invalid entry removed from " & Failed.Address(0, 0)
    End If
  End If
End Sub

 

[Nobby_the_Mouse]

That's fantastic Peter! ...works a treat. Even allows an entry with one space after the latters which I like (e.g. SU 1234567890).

My only worry is (potentially unfounded), is that the spreadsheet is to be used by a wide variety of people with varying skills in Excel (who I have no direct contact with). It's now the case that the sheet is a macro-enabled workbook (*.xlsm), so...

1. Will all versions of Excel allow this code to be used (I can't guarantee that a user will have a version of Excel of 2007 or later).
2. The user now has to 'enable macros' every time they use the spreadsheet? This is an added complication if they don't understand this and continue to enter data without enabling macros.
3. I will be locking the sheet so users cannot access formula and only enter data - might there be any complications with this?

Cheers,
Ben

 

[Peter_SSs]

That's fantastic Peter! ...works a treat. Even allows an entry with one space after the latters which I like (e.g. SU 1234567890).

It should also allow a space at the mid-point of the numerical part per your initial request.

1. Will all versions of Excel allow this code to be used (I can't guarantee that a user will have a version of Excel of 2007 or later).

This shouldn't be a problem (so long as we aren't talking Mac versions)

2. The user now has to 'enable macros' every time they use the spreadsheet? This is an added complication if they don't understand this and continue to enter data without enabling macros.

This could be an issue, though they don't necessarily have to do this each time. There are various settings and options depending on what Excel version the user has. You might also search around for "force enable macros" to see if any of the various suggestions might suit your circumstances.

3. I will be locking the sheet so users cannot access formula and only enter data - might there be any complications with this?

Having the sheet protected shouldn't be a problem as you already have to allow the users to edit the relevant cells.

 

[Nobby_the_Mouse]

Fab - thanks for your help, much appreciated. Yes - allows the mid-point spacing too which is great. I'll do some research and try figure out a way to force enable macros - thanks for the pointer.

The spreadsheet will prevent entry of incorrect wildlife data potentially saving hundreds of man hours and never ending confusion - so thank you for your help and I hope the VBA code comes in useful for others in the future too

Best wishes,
Ben

 

[Peter_SSs]

Fab - thanks for your help, much appreciated.

You are welcome.

Probably not worth changing but the last part of that 'Pattern' line could be written a little more succinctly:

& "(( ?\d{2}){2}|( ?\d{3}){2}|( ?\d{4}){2}|( ?\d{5}){2})$"