Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Cell cannot be blank (Office 97)

    Hi
    I have a cell with data validation you can only enter an X or 1, but you can also leave it blank, I do not wish for it to be blank, it must contain either X or 1 Entered by the user.

    Any help gratefully received

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Cell cannot be blank (Office 97)

    The Data | Validation dialog has a check box to ignore blank cells. If you clear this check box, the user won't be able to clear the cell once it has been filled. If you want the cell to be filled at all times, enter a value in it yourself. When the user edits the spreadsheet, the cell will already be filled, and the validation will ensure that it can't be cleared.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cell cannot be blank (Office 97)

    Select the cell
    Data - validation
    Allow:list
    X,1
    Uncheck "ignore blank"
    <ok>

    Note: if they go to the cell they cannot leave it blank, but if they never edit the cell it can remain blank with no problem.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell cannot be blank (Office 97)

    Hi

    Thanks to Hans and Steve for the prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell cannot be blank (Office 97)

    Hi Steve

    Sorry to be a pain but I tried what you suggest, but I can stil leave the cell blank, where have I gone wrong/

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Cell cannot be blank (Office 97)

    I'm sorry, I didn't understand the meaning of the "Ignore Blanks" check box correctly; it only controls whether you can clear a cell after entering a value not in the list. Ordinarily, validation will allow you to clear a cell. In other words, you cannot use validation to prevent a cell from being cleared. I hadn't noticed that myself before.

    It may be possible to use a worksheet event procedure to handle this, but it may be better to tackle the problem at the root. In your other recent thread, you mentioned that you want to avoid blank cells because they become 0 in a link formula. You can do this by adapting the link formula:

    <code>=IF(SheetName!A1="","",SheetName!A1)</code>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cell cannot be blank (Office 97)

    I had always been mistaken about it also (obviously). I guess it goes to show how you fail to learn the little tricks when you never use it.

    I even tried the custom formula (in A1):
    =AND(OR(A1="X",A1=1),NOT(ISBLANK(A1)))

    and it still allowed me to clear the contents, even though if checked it marked it as invalid.

    Personally, I have never been very impressed with validation. It can be "gotten around" by accidently by simply copying a cell with no validation over it, makes it not very "safe" for use since many people know how to copy and paste.

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Cell cannot be blank (Office 97)

    Hi

    Once again thanks to Hans and Steve for their replies.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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