Software

Ask something
Discussion Locked

Excel-validate total

0 helpful
5 replies

by whatme?

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

All number amounts. column A - A1:A4= total in A6
column B B1:B4 = total in B6
In D column D1:D7 I break down numbers in A and B column to come up with the same combined total of A and B column. This total is placed in D9. What I need is some sort of validation so as to arrive at the same totals (D9= (A6+B6) and not let a person enter more or less than the total of D9 in the D column.
Confusing?

5 replies

by timwalsh

Yes, very. What goal are you trying to achieve?

If I understand you correctly, all the numbers in the D column are calculated totals derived from A and B column. Therefore, users won't be entering anything in D column.

Just to make sure I totally understand what you are doing:
1. Users enter some numbers in column A and some numbers in column B.
2. The numbers in coumn A are summed in cell A6. Likewise with the numbers in column B.
3. Column D you are using as some sort of cross check. The sum of A1:B1 is calculated and placed in D1. All numbers in column D are summed and the total is placed in D9. You want to validate that the sum in D9 is equal to the sum of A6:B6.
Is this all essentially correct? Are there numbers other than user input included in the totals (A6, B6, D9).

In another cell of your choosing, you can always place an IF statement to verify that the D9=A6+B6 and display a message if they do not match. However, assuming users are only entering numbers in columns A and B and no other numbers are included in the sums mentioned, then the sums will always match.

Or am I missing something here?

by whatme?

Your close. In A column your right. In B column your right. But in D column I separate some of A and B columns and insert several amounts. What I am after is to make sure that the entries in the D column match the total of (A and B columns)

by timwalsh

You will not be able to use true data validation because the validation rule will go into effect as soon as you input the your first number, essentially preventing you from inputting other numbers.

If you apply data validation to D9, then the first number input by a user will cause a validation error due to a lack of any numbers in column D to sum.

If you apply data validation to the cells in column D which you use. The first number you input will invoke the validation rule and preventyou from inputting numbers in any other celss without receiving a validation error.

What I would suggest is that you pick a cell (say D10) and input the following function:
=IF(D9=SUM(A6:B6),"Totals Match",IF(D9>SUM(A6:B6),"D9 is larger",IF(D9SUM(A6:B6) is true, or it will show the string "D9 is smaller" if the argument D9>SUM(A6:B6) is true. While this will not prevent you from inputting a number that causes the 2 sums to not equal, it will tell let you know whtther the sums match or if D9is larger or smaller.

Hope this helps.

by whatme?

Helps. Not quite the way I had planned but it helps. If you wish I could email you the excel file to show you why I would like it.
Thanks
MTulloch@nulas.ca

This discussion is closed to new replies.