Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Validate for blank? (Excel 2000 >)

    Is it possible to validate for a blank? In the sample W/B, I would like to ensure that the ID is only valid if there is a name in the A column!
    Any suggestions!
    Regards,
    Rudi

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validate for blank? (Excel 2000 >)

    Thanks. It is working now, but I wanna ask you something about it tomorrow. I need to leave now.
    Cheers
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validate for blank? (Excel 2000 >)

    Hans, I see you seperated the arguments with ;? Is your excel appl. set up that way??

    Also, its interesting to see how you solved this problem. By the use of If, and by purposefully using the ISBLANK to return false in the true statement....This is very artistic!
    Cheers
    Regards,
    Rudi

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

    Re: Validate for blank? (Excel 2000 >)

    I'm sorry, that's because we use a comma as decimal separator and semicolon as list separator in The Netherlands. You should use a comma as separator.

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

    Re: Validate for blank? (Excel 2000 >)

    Try this formula in the Data | Validation dialog for B5:B20

    =IF(ISBLANK(A5);ISBLANK(B5);LEN(B5)=4)

    and make sure to clear the Ignore Blanks check box, otherwise validation is canceled if a cell in the formula is blank.

    Added: the separator in the formula should be the list separator - usually a comma instead of the semicolon I used.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validate for blank? (Excel 2000 >)

    I've never seen the sample functions you post in the lounge use semi colons before. Do you usually change it before you post them? Also, is excel affected if you change the comma to a semi colon in the regional options? I recall another company that I trained at had excel use semi colons, and I couldn't figure out why!
    Regards,
    Rudi

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

    Re: Validate for blank? (Excel 2000 >)

    I have a custom toolbar button (thanks to Jan Karel Pieterse) that puts the English version of the formula in the active cell on the clipboard, ready to be pasted into a post. But sometimes I'm lazy or careless and forget to use it.

    Excel automatically adapts the decimal separator, thousands separator and list separator to the settings in the Regional and Language Options control panel. The same holds for the name of built-in worksheet functions (unfortunately not for functions in the Analysis ToolPak). So if I post a workbook with Dutch settings, it will use your settings when you open it.

    Note: recent versions of Excel have an International tab in Tools | Options... You can specify which decimal separator and thousands separator you want to use.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validate for blank? (Excel 2000 >)

    Thanx.

    PS: I must admit that I find it intriguing how loungers need to adapt based on their home language, and if they are using different langauge software! I often need to remind myself that I must expand my thoughts as its common to think of "me in my small corner" when there is a big wide world out there!

    Cheers
    Regards,
    Rudi

Posting Permissions

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