General discussion

  • Creator
    Topic
  • #2309869

    Excel-validate total

    Locked

    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?

All Comments

  • Author
    Replies
    • #3467861

      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?

      • #3467859

        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)

    • #3467834

      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(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.

      • #3468722

        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

    • #3468721

      Excel-validate total

      by whatme? ·

      In reply to Excel-validate total

      This question was closed by the author

Viewing 2 reply threads