Rob
I put the following together to achieve exactly what you are asking:
Cells A5 thro' to A11 - enter labels: 1st No., 2nd No., 3rd No. and so on to
7th No.
Cell A13 - enter label: Last 2 Nos.
Put Boxes around cells B5 to B11 and B13
Cell B13 goto data validation and enter formula:
=AND(B130,B13=D13*-1) and format to a colour (yellow?)
Add second condition:
=D13<0 and format to a colour (red?)
OK
Now in
Cell D5 enter =B5*8
Cell D6 enter =B6*7
Cell D7 enter =B7*6
Cell D8 enter =B8*5
Cell D9 enter =B9*4
Cell D10 enter = B10*3
Cell D11 enter = B11*2
Cell D12 enter =SUM(D5:D11)
Cell D13 enter
=IF(B13="",0,IF(D12-97<0,D12-97,IF(D12-97-97<0,D12-97-97,D12-97-97-97)))
Either hide column D or format D5:D13 white to hide calculation.
Then in Cell B13 go to Data Validation, Settings tab and enter:
Allow: Whole Number - tick Ignore Blank
Data: equal to
Value: =D13*-1
Error Alert tab - tick Show alert after invalid data is entered
Style: Stop
Title: ERROR
Error message: VAT Number is not valid
OK
You could then finish off the chart:
Cell A1 "VAT NUMBER VALIDATION"
Cell A2 "Enter VAT number below"
Cell A3 "A valid number produces a yellow box"
Now enter the VAT number to be tested, one digit in each of cells B5 thro'
to B11 and the last two digits in B13.
If the number is valid the cell B13 will turn yellow else if not valid the
cell will turn red and a small ERROR window will appear telling you the VAT
number is nit valid.
Hope you will find this of some use, I do.
I guess to be really sophisticated you could have a button to press (run a
macro) to clear the entries ready for a new number, but I'll let somebody
else tell you how to do that, if you don't know.


"Rob" wrote:

Hi,

I want to check a number of VAT numbers using a formula, the first check is
to ensure the number is 9 digits (may need to remove spaces to ensure clean
data). The process is then to apply the below criteria which has proved
difficult for he to accomplish.

Any help will be most welcome. Thanks, Rob
1.. The first seven digits of the VAT registration number are listed
vertically.
2.. Each digit is multiplied by a number, starting with 8 and decreasing
to 2.
3.. The sum of the multiplications is calculated.
4.. 97 is subtracted from the sum as many times as is necessary to arrive
at a negative number.
5.. The negative number should be the same as the last 2 digits of the VAT
registration number if it is valid.
Example:

VAT registration number 339 0727 47

3 *8 = 24
3 *7 = 21
9 *6 = 54
0 *5 = 0
7 *4 = 28
2 *3 = 6
7 *2 = 14

Total = 147

147 - 97 = 50 - 97 = - 47

As the negative number(- 47) is the same as the last two digits of the VAT
number, the number is valid.

1.. The first seven digits of the VAT registration number are listed
vertically.
2.. Each digit is multiplied by a number, starting with 8 and decreasing
to 2.
3.. The sum of the multiplications is calculated.
4.. 97 is subtracted from the sum as many times as is necessary to arrive
at a negative number.
5.. The negative number should be the same as the last 2 digits of the VAT
registration number if it is valid.


.