Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    custom validation formula

    Hi All,

    I'm trying to create a custom validation formula (Excel 2003). It seems like it should be easy but "easy" isn't working.

    I have a cell S4 where, via dropdown box, I can set a reason for closing; the list for the dropdown is stored in a range and has the values nothing (an empty cell), "snow", etc. All reason codes, other than nothing, are at least 3 characters in length.

    In cell C4, I want to allow entries as follows:
    - if S4 is not nothing, don't allow any entries
    - if S4 is nothing, allow either "out" or a positive number

    For my custom validation rule, I have:
    =AND(LEN(S4)<3,OR(C5="out",AND(ISNUMBER(C5),C5>0)) )

    With this, anything seems to be allowed when LEN(S4)<3 (2nd dash above) - this is NOT OK. If LEN(S4)>=3 (first dash above), nothing is allowed (this is OK).

    If I delete the test on S4 (not really the end of the world), it works fine in allowing only "out" or a positive number.

    What am I missing?

    TIA

    Fred

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Not sure I fully understand, but suppose you changed LEN(S4) to ISBLANK(S4) ??

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fred,

    This formula works according to your specs.
    =AND(ISBLANK(S4),OR(C5="out",C5>0))

    Note: If you change S4 after entering C4 it will NOT re-validate!
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks guys but I'm not sure I appreciate the difference. I checked the length of S4 when there was nothing in there (just LEN(S4) in a separate cell) and got 0.

    Would ISBLANK even work when the length really is 0?

    But why would ISBLANK(S4) work and not LEN(S4)<3?

    I originally had just C5>0 w/o the ISNUMBER test but that wasn't working either.

    RG: Understand the part about C5 not re-validating if S4 changed after it. The logic of the spreadsheet is that you'd enter S4 first. If you've made an entry, you would not bother entering anything for C5 anyway. S4 is a reason for the entire school being closed on a given day, so it affects all teachers. C5 is an individual teacher, who may have been "out" or worked some # of hours that day but the school was open. There's also a D5, E5, etc for other teachers.

    Fred

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fred,

    Actually, this does work
    =AND((LEN($S$4)<3),(OR($C$5="out",$C$5>0)))

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    ..if you put "banana" in cell $C$5 and then, in another cell, put the formula =$C$5>0 then this formula will give a result of True. So the (OR($C$5="out",$C$5>0)) will also give True

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi fred

    I don't think using a custom validation formula is the right approach for what you are doing.
    But, nevertheless, to give you what you ask for, see attached file.

    This allows you to select 'Closing Reason' from a dropdown list in cell S4.
    In cell C4, you can then select from another dropdown:
    > if S4 is not nothing, you can only 'select' a blank entry.
    > if S4 is nothing, you can select either "out" or a positive number (I made a list of 1 to 12 as an example)

    Note, as per RG's comment, if you change S4 after entering C4, if the value in C4 is now 'invalid', it will then be 'circled'.
    (This uses the worksheet change event to CircleInvalid)

    My example file uses adjacent formulas in columns T, U to determine which dropdown is allowed for the entry in C4. The data validation for C4 then uses an 'indirect' formula to give the allowed entries from the allowed list.

    If, as I suspect, you might require further entries below C5 and S4, you might prefer to just use event-trapping to control the allowed entries in column C

    zeddy
    Attached Files Attached Files

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,206
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Quote Originally Posted by zeddy View Post
    I don't think using a custom validation formula is the right approach
    What do you suggest instead?

    cheers, Paul

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    you might prefer to just use event-trapping to control the allowed entries in column C
    zeddy

  10. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,206
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Sorry, not looking properly.

    cheers, Paul

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    This is crazy! If a cell contains text it should have a value of zero but obviously this isn't always what Excel calculates it as!

    For instance: =(c5+1)>0 returns #VALUE! when c4 contains "banana".
    However: =Sum(c5)>0 returns FALSE

    It gets crazier!

    This formula: =AND(ISBLANK(S4),OR(C5="out",(SUM(C5)>0))) returns the correct value if placed in a cell and the cells (S4 - C5 - C4) are manipulated.

    However, if the formula is placed in the Data Valadation it does not work! But, if you just reference in the Data Validation, e.g. the formula is in F4 and the Data Validation formula is set to =F4 the data validation works correctly, go figure?

    HTH
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    SUM ignores text, whereas operators (+ - / *) don't, hence you get 0 for SUM("some text") and an error if you try and use a mathematical operator.

    When using a formula in DV you really need to uncheck the Ignore Blank option.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2015-03-02)

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Who knew!

    I rarely use DV as I usually use VBA for checking. That sure fixed the problem and now the formula works directly in DV.

    Can you elaborate more on why this is so?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Who knew
    Me?

    Can you elaborate more on why this is so?
    Not really - I think it's a bug but I confess I've not bothered to report it as the workaround is so trivial, and there may conceivably be a reason for it.
    Regards,
    Rory

    Microsoft MVP - Excel

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    ..you could use
    N(C5)>0
    instead of..
    SUM(C5)>0

    So N(C5)>0 could replace AND(ISNUMBER(C5),C5>0)

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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