General discussion

Locked

Excel-validate total

By Whatme? ·
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?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel-validate total

by timwalsh In reply to Excel-validate total

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?

Collapse -

Excel-validate total

by Whatme? In reply to Excel-validate total

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)

Collapse -

Excel-validate total

by timwalsh In reply to Excel-validate total

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(D9<SUM(A6:B6),"D9 is smaller")))

Using this function cell D10 will show one of three things at any given time: it will show the string "Totals match" if the argument D9=SUM(A6:B6) is true, it will show the string "D9 is larger" if the argument D9>SUM(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.

Collapse -

Excel-validate total

by Whatme? In reply to Excel-validate total

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

Collapse -

Excel-validate total

by Whatme? In reply to Excel-validate total

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums