COVID - Government Bulk Test Kit Registration Result Template

[timbo343]

I have been asked to look at the Government's Spreadsheet of the Bulk Test Kit Registration Result Template and we are struggling to get a few of the columns to work - i was wondering if anyone else has had this problem?

 

We are using Google Sheets to add the information to the spreadsheet and finding the following are allowing us to input the correct information to due a data validation rule yet the information we are entering is correct.

 

Are we allowed to remove the data validation on the columns that are giving us errors?

 

The issues are as follows:

 

Issue 1:

 

Column C = Date of Test "Enter as DD/MM/YYYY". We enter the date (today's date) and it throws an error up - There was a problem - Enter the data in the format DD/MM/YYYY. IT must be within the last 7 days or tomorrow's date. The data validation formula is:

 

=AND(ISNUMBER(C5),LEFT(CELL("format",C5),1)="D", C5<=TODAY()+1, C5>=((TODAY()+1)-7))

 

 

Issue 2:

 

Column F = Date symptoms started "Only enter if showing Symptoms - Enter as DD/MM/YYYY" - best part of this is, it's a drop down menu with times which is being pulled from a hidden sheet.

 

 

Issue 3:

 

Column J = Date of Birth "Enter as DD/MM/YYYY" - entering a correct DOB provides the error - There is a problem - Enter a date in the past in the format DD/MM/YYYY.

 

=AND(ISNUMBER(J5),LEFT(CELL("format",J5),1)="D", J5<=TODAY())

 

 

Issue 4:

 

Column O = Post Code - entering a correct postcode gives the error - There is a problem - Enter the postcode in one of the following formats: AA9A 9AA; A9A 9AA; A9 9AA; A99 9AA; AA9 9AA; AA99 9AA. The data validation is looking up Column AC which is the following formula:

 

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(OR(
N("VALIDATION FOR UK POSTCODES"),
OR(
IF(LEN(O5)=6, O5="gir0aa", FALSE), N("For the special case of Girobank"),
IF(LEN(O5)=7, O5="gir 0aa", FALSE), N("For the special case of Girobank"),

AND(
LEN(O5)>=5,
LEN(O5)<=8,
LEN(O5)-LEN(SUBSTITUTE(O5," ",""))<=1, NOT(N("Must have 1 or fewer spaces")),
SUM(LEN(O5)-LEN(SUBSTITUTE(O5,{1,2,3,4,5,6,7,8,9,0},)))<=3, NOT(N("Must have 3 or fewer numbers")),
SUM(LEN(O5)-LEN(SUBSTITUTE(UPPER(O5),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},)))<=5, NOT(N("Must have 5 or fewer letters")),

ISNUMBER(SEARCH(MID(O5, 1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),NOT(N("The first character must be A-Z or a-z")),
ISNUMBER(SEARCH(MID(O5, 2,1),"ABCDEFGHJKLMNOPQRSTUVWXY1234567890")),NOT(N("The second character must be a letter or number, but cannot be I or Z")),
IF(ISNUMBER(SEARCH(MID(O5, 2,1),"ABCDEFGHJKLMNOPQRSTUVWXY")), ISNUMBER(NUMBERVALUE(MID(O5,3,1))), ISNUMBER(SEARCH(MID(O5, 2,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"))),
NOT(N("The third character must be a number only if the second character is a letter, if not then can be letter or number")),

OR(AND(CODE(MID(O5,4,1))>=65,CODE(MID(O5,4,1))<=90),AND(CODE(MID(O5,4,1))>=97,CODE(MID(O5,4,1))<=122),ISNUMBER(NUMBERVALUE(MID(O5,4,1))),MID(O5,4,1)=" "),NOT(N("The fourth character must be a letter or number or space")),
IF((LEN(O5)-LEN(SUBSTITUTE(O5," ","")))=1,MID(O5,LEN(O5)-3,1)=" ",TRUE),NOT(N("If there is a space, it must only be the fourth last character")),
NOT(AND(ISNUMBER(SEARCH(MID(O5, LEN(O5)-4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), ISNUMBER(SEARCH(MID(O5, LEN(O5)-3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))), NOT(N("The fourth and fifth last characters cannot both be letters")),

ISNUMBER(NUMBERVALUE(MID(O5,LEN(O5)-2,1))),NOT(N("The third last character must be a number")),
ISNUMBER(SEARCH(MID(O5, LEN(O5)-1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),NOT(N("The second last character must be a letter")),
ISNUMBER(SEARCH(MID(O5, LEN(O5),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),NOT(N("The last character must be a letter"))
)),

N("VALIDATION FOR ROI POSTCODES"),
AND(
OR(ISNUMBER(SEARCH(MID(O5, 1, 3),"D6W")), N("Only exception where first 3 characters are allowed to be D6W"),
AND(ISNUMBER(SEARCH(MID(O5,1,1),"ACDEFHKNPRTVWXY")), NOT(N("The first character must be from the set of letters")),
ISNUMBER(NUMBERVALUE(MID(O5,2,1))), NOT(N("The second character must be a number")),
ISNUMBER(NUMBERVALUE(MID(O5,3,1))), NOT(N("The third character must be a number")))),

ISNUMBER(SEARCH(MID(O5, 4,1),"ACDEFHKNPRTVWXY1234567890 -")), NOT(N("The fourth character must be from the set of letters, a space, or a dash")),
IF(LEN(O5)=8, OR(MID(O5, 4, 1)=" ", MID(O5, 4, 1)="-"), TRUE), NOT(N("If the length is 8 then the fourth character must be a space or dash")),
SUM(LEN(O5)-LEN(SUBSTITUTE(O5," ","")), LEN(O5)-LEN(SUBSTITUTE(O5,"-","")))<=1, NOT(N("If there is a space or dash, there must only be one")),
IF((LEN(O5)-LEN(SUBSTITUTE(O5," ","")))=1, MID(O5,4,1)=" ", TRUE), NOT(N("If there is a space, it must be the 4th character")),
IF((LEN(O5)-LEN(SUBSTITUTE(O5,"-","")))=1, MID(O5,4,1)="-", TRUE), NOT(N("If there is a dash, it must be the 4th character")),

ISNUMBER(SEARCH(MID(O5,LEN(O5)-3,1),"ACDEFHKNPRTVWXY1234567890")), NOT(N("The fourth last character must be from the set of letters or a number")),
ISNUMBER(SEARCH(MID(O5,LEN(O5)-2,1),"ACDEFHKNPRTVWXY1234567890")), NOT(N("The third last character must be from the set of letters or a number")),
ISNUMBER(SEARCH(MID(O5,LEN(O5)-1,1),"ACDEFHKNPRTVWXY1234567890")), NOT(N("The second last character must be from the set of letters or a number")),
ISNUMBER(SEARCH(MID(O5,LEN(O5),1),"ACDEFHKNPRTVWXY1234567890")), NOT(N("The last character must be from the set of letters or a number")),

LEN(O5)>=7, LEN(O5)<=8,
IF(MID(O5,1,3)="D6W", TRUE, SUM(LEN(O5)-LEN(SUBSTITUTE(O5,{1,2,3,4,5,6,7,8,9,0},)))<=6), NOT(N("Must have 6 or fewer numbers")),
IF(MID(O5,1,3)="D6W", TRUE, SUM(LEN(O5)-LEN(SUBSTITUTE(UPPER(O5),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},)))<=5), NOT(N("Must have 5 or fewer letters"))
)
), FALSE)), 1, 1)

 

 

Issue 5:

 

Mobile phone number - entering a valid UK phone number gives an error - There is a problem - Enter the mobile number without any spaces in the following format: UK mobile - 07XXXXXXXXX, international without '+' or spaces, for example, 447XXXXXXXXX, US or CA number - 001XXXXXXXXXX, ROI - 353XXXXXXXXX. Here is the validation formula for this column:

 

=AND(LEN(V5)=LEN(SUBSTITUTE(V5," ","")) + N("Make sure there are no spaces in the phone number"),
LEN(V5)=LEN(SUBSTITUTE(V5,"+","")) + N("Make sure there are no + signs"),
VALUE(V5) + N("Make sure there are only numbers"),
OR(
AND(LEN(V5)=10,MID(V5,1,1)="1"),
LEN(V5)=13,
AND(LEN(V5)=11,MID(V5,1,2)="07"),
LEN(V5)=12,
AND(LEN(V5)=13,MID(V5,1,3)="353"),
LEN(V5)=14))

Edited December 2, 2021 by timbo343

[David44]

Was this an Excel spreadsheet that you have converted to a Google Sheet?

 

Looking at the CELL function in issue 1, this is checking that that what it returns starts with a D which in Excel means it is a date formatted cell (https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf) but 'D' is not used in Google Sheets (https://support.google.com/docs/answer/3267071).

 

You could rewrite all the validation rules but I wonder what format you have to upload the file in? If it's CSV, maybe it's easier just to remove the validation rules and let them tell you what's wrong when you come to upload the file.

[timbo343]

Was this an Excel spreadsheet that you have converted to a Google Sheet?

 

Looking at the CELL function in issue 1, this is checking that that what it returns starts with a D which in Excel means it is a date formatted cell (https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf) but 'D' is not used in Google Sheets (https://support.google.com/docs/answer/3267071).

 

You could rewrite all the validation rules but I wonder what format you have to upload the file in? If it's CSV, maybe it's easier just to remove the validation rules and let them tell you what's wrong when you come to upload the file.

Yeah it was an XLSX file that was shared through Google Drive. We couldn't edit it on Google Drive via the shared folder (from what we think is the government) so we downloaded the XLSX file and re-uploaded it to our google drive so we can have multiple people editing the spreadsheet - it's still as an XLSX file on Google Drive.

 

We could upload the XLSX to OneDrive and edit there which might be a possibility. UPDATE: The formula FORMAT doesn't work on Excel Online [emoji19].

 

So i've edited the data validation for Issue 1 to:

 

=AND(ISNUMBER(C5),C5=((TODAY()+1)-7))

 

And it seems to work, well it doesn't give the error anymore but it gives an error if the date is out of range.

Edited December 2, 2021 by timbo343

[Ditto]

Just on the postcodes, this sort of thing annoys the historical @Ditto programmer - the solution is good, but it could be better. Valid format does not equal valid post code. An external call via an API would be far better - for example see https://postcodes.io/

 

As an aside, how did you get to access the formulas - aren't they locked with a password?

[timbo343]

Just on the postcodes, this sort of thing annoys the historical @Ditto programmer - the solution is good, but it could be better. Valid format does not equal valid post code. An external call via an API would be far better - for example see https://postcodes.io/

 

As an aside, how did you get to access the formulas - aren't they locked with a password?

 

Everything is open and editable - there's no passwords required to look at any of the formulas.

 

I am thinking of removing all the validation for the purpose of collaboration whilst we populate the Google sheet.

 

My biggest worry is, if we download the XLSX from Google drive and upload it to the government, will they be bothered that we have removed the validation on some columns?

[Ditto]

I think maybe I have a different d/l from the gov site as the one I found is password protected. Have you a link to the source of your file. I know with the one I found, it is xlsx only, but really they should provide ODS too.

[timbo343]

I think maybe I have a different d/l from the gov site as the one I found is password protected. Have you a link to the source of your file. I know with the one I found, it is xlsx only, but really they should provide ODS too.

 

@Ditto Here is a screen grab of the validation formulas:

 

[Ditto]

I'm on 2013 so command sequences may be a little different but I think they should all have an equivalent.

This has got a little more interesting! I d/l the file you shared as an Excel Workbook and tried to work from there. It's definitely protected. Go to Review and you click Unprotect sheet and it ask for a password. In this Excel file, I can't even get to column Z. In fact if I am in column Y and press right arrow, it does that really annoying thing of jumping to Column A on the next row. That's usually achieved with VBA code which I can't see. It also means the data validation button is grey out.

On the xlsx file that you shared read-only, I can get to column Z onwards and can see your formulas. I think Data validation and formulas in Excel and Google Sheets are slightly different even if they can achieve the same thing. None of the drop down lists show up either.

Now, I copied your shared Google Drive file to mine. Now I can see all the drop down lists and see all the validation code. This seems to be a way of by-passing the locked sheet which feels somewhat suspect (Having checked, it's a documented work-around and you can do the same with Open Office).

What you can see now is all the drop down lists use a hidden sheet called REFERENCE. If you click right on any sheet, (in Google Drive or the Excel), you can unhide it.

So now back to the issues.

 

• Issue 1 - @David44 has covered - you can just change to warning and perhaps turn it back on for final saving
  
• Issue 2 - I couldn't reproduce this one - it's as if it's got the validation column C.
  
• Issue 3 - basically the same as Issue 1
  
• Issue 4 - this would take a fair bit to resolve - see https://infoinspired.com/sheets-vs-excel-formula/array-formula-differs-in-google-sheets-and-excel/
  
• Issue 5 - this is weird. On the face of it the validation looks fine. If you enter a valid 07 mobile number it rejects. If you change the data to warning rather than reject, it accepts it.
  

 

If you set the font colour of column Z to black, it's easier to see. You'll have to ask google about this one. To conclude, maybe you could sort a native Google sheet out, but it'll be hard work. Perhaps to support multiple input, give each data entry person a Google sheet and combine after.