Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indirect Data Validation (Excel 2000)

    I want to have some form of data validation in the following way: Cell A1 is the sum of A2 and A3. When entering data in A2 and A3, the sum of A2 and A3 must not exceed the amount in A1. If we define A1=1, then A2+A3 must not exceed 1. If A2=0.9, then A3 must not exceed 0.1. Any way to do this ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Indirect Data Validation (Excel 2000)

    I assume that you don't mean "Cell A1 is the sum of A2 and A3" literally, for that would lead to a tautology: if A1 = A2 + A3, the sum of A2 and A3 is by definition equal to A1, so it does not exceed A1.

    Select A2 and A3
    Select Data | Validation...
    Select Custom from the first dropdown list
    Enter =$A$2+$A$3<=$A$1 in the formula box.
    Set an error message.
    Click OK.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Data Validation (Excel 2000)

    Sure:

    1- Select cells A2 and A3.

    2- Select Validation from the Data menu.

    3- Click the Settings tab.

    4- In the Allow drop down list select Custom.

    5- In the Formula box enter the formula below:

    <pre>=(A$2+A$3)<=A$1
    </pre>


    6- Click the Error Alert tab and enter the message you want displayed if A2+A3>A1.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Data Validation (Excel 2000)

    Thanks to Hans and Coleman !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •