+ Reply to Thread
Results 1 to 11 of 11

Data Validation - ignore blanks

  1. #1
    Neville
    Guest

    Data Validation - ignore blanks

    I have set up a data form and have data validation rules on the various input
    cells. I see that there is an option called "Ignore blanks" in the data
    validation settings, which is supposed to restrict someone from entering past
    an input cell without entering valid data, if the "Ignore blanks" box is
    unchecked. I have tried to do this, but it does not seem to work. Please
    could someone tell me what I am doing wrong?

  2. #2
    JMMach
    Guest

    Re: Data Validation - ignore blanks

    Provide information, or better yet, a screen capture of the Data Validation
    dialog box with the Setting tab selected (User ALT+PrintScreen to copy the
    image to the Windows Clipboard, then paste into your post reply and indicate
    what range was selected when you defined the Data Validation..
    I need to see how you defined the settings then I might be able to tell you
    where you are missing the boat.
    TTFN
    JMMach, MOUS Master

    "Neville" <[email protected]> wrote in message
    news:[email protected]...
    > I have set up a data form and have data validation rules on the various

    input
    > cells. I see that there is an option called "Ignore blanks" in the data
    > validation settings, which is supposed to restrict someone from entering

    past
    > an input cell without entering valid data, if the "Ignore blanks" box is
    > unchecked. I have tried to do this, but it does not seem to work. Please
    > could someone tell me what I am doing wrong?




  3. #3
    Neville
    Guest

    Re: Data Validation - ignore blanks

    Hi JMMach,

    I have tried to paste the screenshot, but the Paste option is greyed out
    when I try to paste it here.

    "JMMach" wrote:

    > Provide information, or better yet, a screen capture of the Data Validation
    > dialog box with the Setting tab selected (User ALT+PrintScreen to copy the
    > image to the Windows Clipboard, then paste into your post reply and indicate
    > what range was selected when you defined the Data Validation..
    > I need to see how you defined the settings then I might be able to tell you
    > where you are missing the boat.
    > TTFN
    > JMMach, MOUS Master
    >
    > "Neville" <[email protected]> wrote in message
    > news:[email protected]...
    > > I have set up a data form and have data validation rules on the various

    > input
    > > cells. I see that there is an option called "Ignore blanks" in the data
    > > validation settings, which is supposed to restrict someone from entering

    > past
    > > an input cell without entering valid data, if the "Ignore blanks" box is
    > > unchecked. I have tried to do this, but it does not seem to work. Please
    > > could someone tell me what I am doing wrong?

    >
    >
    >


  4. #4
    JMMach
    Guest

    Re: Data Validation - ignore blanks

    How about attaching Excel file, or if it has sensitive information then
    paste the dialog box into Word or WordPad and attach that to the message.
    TTFN
    JMMach
    "Neville" <[email protected]> wrote in message
    news:[email protected]...
    > Hi JMMach,
    >
    > I have tried to paste the screenshot, but the Paste option is greyed out
    > when I try to paste it here.
    >
    > "JMMach" wrote:
    >
    > > Provide information, or better yet, a screen capture of the Data

    Validation
    > > dialog box with the Setting tab selected (User ALT+PrintScreen to copy

    the
    > > image to the Windows Clipboard, then paste into your post reply and

    indicate
    > > what range was selected when you defined the Data Validation..
    > > I need to see how you defined the settings then I might be able to tell

    you
    > > where you are missing the boat.
    > > TTFN
    > > JMMach, MOUS Master
    > >
    > > "Neville" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > I have set up a data form and have data validation rules on the

    various
    > > input
    > > > cells. I see that there is an option called "Ignore blanks" in the

    data
    > > > validation settings, which is supposed to restrict someone from

    entering
    > > past
    > > > an input cell without entering valid data, if the "Ignore blanks" box

    is
    > > > unchecked. I have tried to do this, but it does not seem to work.

    Please
    > > > could someone tell me what I am doing wrong?

    > >
    > >
    > >




  5. #5
    Neville
    Guest

    Re: Data Validation - ignore blanks

    How does one attach a file to a posting?

    "JMMach" wrote:

    > How about attaching Excel file, or if it has sensitive information then
    > paste the dialog box into Word or WordPad and attach that to the message.
    > TTFN
    > JMMach
    > "Neville" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi JMMach,
    > >
    > > I have tried to paste the screenshot, but the Paste option is greyed out
    > > when I try to paste it here.
    > >
    > > "JMMach" wrote:
    > >
    > > > Provide information, or better yet, a screen capture of the Data

    > Validation
    > > > dialog box with the Setting tab selected (User ALT+PrintScreen to copy

    > the
    > > > image to the Windows Clipboard, then paste into your post reply and

    > indicate
    > > > what range was selected when you defined the Data Validation..
    > > > I need to see how you defined the settings then I might be able to tell

    > you
    > > > where you are missing the boat.
    > > > TTFN
    > > > JMMach, MOUS Master
    > > >
    > > > "Neville" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > I have set up a data form and have data validation rules on the

    > various
    > > > input
    > > > > cells. I see that there is an option called "Ignore blanks" in the

    > data
    > > > > validation settings, which is supposed to restrict someone from

    > entering
    > > > past
    > > > > an input cell without entering valid data, if the "Ignore blanks" box

    > is
    > > > > unchecked. I have tried to do this, but it does not seem to work.

    > Please
    > > > > could someone tell me what I am doing wrong?
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Debra Dalgleish
    Guest

    Re: Data Validation - ignore blanks

    Unchecking 'Ignore blanks' doesn't force users to enter a value in a cell.

    No matter what data validation settings you've applied, users will be
    able to leave the cell blank, or select the cell, and press the Delete
    key, to clear the cell.

    However, if the cursor is in the cell, or in the formula bar, they won't
    be able to press the Delete key, to clear the cell, or press the Enter
    key while the cell is blank.

    You could use programming to check for blank cells, or use conditional
    formatting in conjunction with the data validation. For example, turn
    the other cells in the row to black fill if the cell with data
    validation is blank.

    If the cell with data validation in cell A2, select cells B2:H2
    Choose Format>Conditional Formatting
    From the first dropdown, choose Formula Is
    In the formula box, type: =$A2=""
    Click the Format button, and on the Pattern tab, select the black colour
    Click OK, click OK



    Neville wrote:
    > I have set up a data form and have data validation rules on the various input
    > cells. I see that there is an option called "Ignore blanks" in the data
    > validation settings, which is supposed to restrict someone from entering past
    > an input cell without entering valid data, if the "Ignore blanks" box is
    > unchecked. I have tried to do this, but it does not seem to work. Please
    > could someone tell me what I am doing wrong?



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  7. #7
    JMMach
    Guest

    Re: Data Validation - ignore blanks

    In the reply to groups window, you will notice an icon with a paper clip,
    click on it and locate the document to be attached.
    If that does not work for you then simply type the details of the Settings
    tab.
    TTFN
    JMMach

    "Neville" <[email protected]> wrote in message
    news:[email protected]...
    > How does one attach a file to a posting?
    >
    > "JMMach" wrote:
    >
    > > How about attaching Excel file, or if it has sensitive information then
    > > paste the dialog box into Word or WordPad and attach that to the

    message.
    > > TTFN
    > > JMMach
    > > "Neville" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Hi JMMach,
    > > >
    > > > I have tried to paste the screenshot, but the Paste option is greyed

    out
    > > > when I try to paste it here.
    > > >
    > > > "JMMach" wrote:
    > > >
    > > > > Provide information, or better yet, a screen capture of the Data

    > > Validation
    > > > > dialog box with the Setting tab selected (User ALT+PrintScreen to

    copy
    > > the
    > > > > image to the Windows Clipboard, then paste into your post reply and

    > > indicate
    > > > > what range was selected when you defined the Data Validation..
    > > > > I need to see how you defined the settings then I might be able to

    tell
    > > you
    > > > > where you are missing the boat.
    > > > > TTFN
    > > > > JMMach, MOUS Master
    > > > >
    > > > > "Neville" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > I have set up a data form and have data validation rules on the

    > > various
    > > > > input
    > > > > > cells. I see that there is an option called "Ignore blanks" in the

    > > data
    > > > > > validation settings, which is supposed to restrict someone from

    > > entering
    > > > > past
    > > > > > an input cell without entering valid data, if the "Ignore blanks"

    box
    > > is
    > > > > > unchecked. I have tried to do this, but it does not seem to work.

    > > Please
    > > > > > could someone tell me what I am doing wrong?
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  8. #8
    Peo Sjoblom
    Guest

    Re: Data Validation - ignore blanks

    Please don't post files in a non binary newsgroup!

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "JMMach" <[email protected]> wrote in message
    news:%[email protected]...
    > In the reply to groups window, you will notice an icon with a paper clip,
    > click on it and locate the document to be attached.
    > If that does not work for you then simply type the details of the Settings
    > tab.
    > TTFN
    > JMMach
    >
    > "Neville" <[email protected]> wrote in message
    > news:[email protected]...
    >> How does one attach a file to a posting?
    >>
    >> "JMMach" wrote:
    >>
    >> > How about attaching Excel file, or if it has sensitive information then
    >> > paste the dialog box into Word or WordPad and attach that to the

    > message.
    >> > TTFN
    >> > JMMach
    >> > "Neville" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> > > Hi JMMach,
    >> > >
    >> > > I have tried to paste the screenshot, but the Paste option is greyed

    > out
    >> > > when I try to paste it here.
    >> > >
    >> > > "JMMach" wrote:
    >> > >
    >> > > > Provide information, or better yet, a screen capture of the Data
    >> > Validation
    >> > > > dialog box with the Setting tab selected (User ALT+PrintScreen to

    > copy
    >> > the
    >> > > > image to the Windows Clipboard, then paste into your post reply and
    >> > indicate
    >> > > > what range was selected when you defined the Data Validation..
    >> > > > I need to see how you defined the settings then I might be able to

    > tell
    >> > you
    >> > > > where you are missing the boat.
    >> > > > TTFN
    >> > > > JMMach, MOUS Master
    >> > > >
    >> > > > "Neville" <[email protected]> wrote in message
    >> > > > news:[email protected]...
    >> > > > > I have set up a data form and have data validation rules on the
    >> > various
    >> > > > input
    >> > > > > cells. I see that there is an option called "Ignore blanks" in
    >> > > > > the
    >> > data
    >> > > > > validation settings, which is supposed to restrict someone from
    >> > entering
    >> > > > past
    >> > > > > an input cell without entering valid data, if the "Ignore blanks"

    > box
    >> > is
    >> > > > > unchecked. I have tried to do this, but it does not seem to work.
    >> > Please
    >> > > > > could someone tell me what I am doing wrong?
    >> > > >
    >> > > >
    >> > > >
    >> >
    >> >
    >> >

    >
    >



  9. #9
    Registered User
    Join Date
    11-08-2005
    Posts
    9
    Quote Originally Posted by Debra Dalgleish
    Unchecking 'Ignore blanks' doesn't force users to enter a value in a cell.
    So what's the purpose of that check box


    Quote Originally Posted by Debra Dalgleish
    ... ...to check for blank cells, or use conditional
    formatting in conjunction with the data validation.
    Conditional formatting is no solution since you can't enter a "IsBlank" or equivalent criteria, only "Cell value Is" or "Formula Is". Do you have more

  10. #10
    Debra Dalgleish
    Guest

    Re: Data Validation - ignore blanks

    Select the cells that you want to format, e.g. F1:F20
    Choose Format>Conditional Formatting
    From the first dropdown, choose Formula Is
    In the formula box, type a formula that refers to the active cell:
    =F1=""
    (you can see the name of the active cell in the Name box, at the left
    of the formula bar)
    Click the Format button, and choose a dark fill colour on the Patterns tab.
    Click OK, click OK


    mcs51mc wrote:
    > Debra Dalgleish Wrote:
    >
    >>Unchecking 'Ignore blanks' doesn't force users to enter a value in a
    >>cell.So what's the purpose of that check box

    >
    >
    >
    > Debra Dalgleish Wrote:
    >
    >>... ...to check for blank cells, or use conditional
    >>formatting in conjunction with the data validation.

    >
    > Conditional formatting is no solution since you can't enter a "IsBlank"
    > or equivalent criteria, only "Cell value Is" or "Formula Is". Do you
    > have more
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  11. #11
    Debra Dalgleish
    Guest

    Re: Data Validation - ignore blanks

    And to colour cells based on the content of another cell in the same
    row, use that cell in the formula, e.g. when formatting cells C1:J1,
    check the contents of cell A1: =$A1=""

    mcs51mc wrote:
    > Debra Dalgleish Wrote:
    >
    >>Unchecking 'Ignore blanks' doesn't force users to enter a value in a
    >>cell.So what's the purpose of that check box

    >
    >
    >
    > Debra Dalgleish Wrote:
    >
    >>... ...to check for blank cells, or use conditional
    >>formatting in conjunction with the data validation.

    >
    > Conditional formatting is no solution since you can't enter a "IsBlank"
    > or equivalent criteria, only "Cell value Is" or "Formula Is". Do you
    > have more
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1