Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Validation Formula (Excel 2000)

    I need to set up a custom validation formula that will disallow entries in cells based on the relationships between two other cells (which can be in two different locations). I explain it in a bit more detail in the attachment, but, basically, the data is set up like this:

    <pre>1.0
    1.1
    <font color=red>A
    B</font color=red>
    1.2
    <font color=red>A</font color=red>
    B
    <font color=red>1
    2</font color=red>
    <font color=red>1.3
    1.4</font color=red>
    2.0
    </pre>



    Those rows which are red here are the only ones that can have entries because they are not parents. Parent rows must be blank. Again, in the attachment, it is explained in a bit more detail.

    I had it set up with checking something like Offset(B2,1,1)<>"" which I think is the right approach, except I ran into trouble trying to make it also check for C2 and, of course, for other rows. My OR's and OFFSET's got too convoluted for me to find my way out again, and all I ended up doing is prevented any entry in any cell. Please shed some of your usual brilliance in this direction!

    --Karyl

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

    Re: Custom Validation Formula (Excel 2000)

    For some reason, I can't get the validation to work by entering the formula directly into the dialog. So I created a range of cells containing the validation formulas. You can hide these cells for the production version. The validation formula for G9 is

    =AND(OR(ISBLANK($B9),ISBLANK($C10)),OR(ISBLANK($C9 ),ISBLANK($D10)),ISBLANK($A9))

    B9 or C10 must be blank, and C9 or D10 must be blank, and A9 must be blank (you can omit the last part if you like). See attached version.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Validation Formula (Excel 2000)

    Many thanks, again! It took me awhile to "see" how it was working, and it drives me nuts that I can't see those logical relationships for myself in a way that I can translate into a formula, but I truly appreciate those of you who can and are willing to share your expertise.

    After much frustrating trial and error, I was finally able to get it to work in the validation dialog by unchecking the "Ignore blanks" box. Again, my heartfelt thanks!

    --Karyl

Posting Permissions

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