+ Reply to Thread
Results 1 to 16 of 16

Adding a blank in Data Validation List?

  1. #1
    Pheasant Plucker®
    Guest

    Adding a blank in Data Validation List?

    Hi there,

    I have a spreadsheet with some cells setup with a drop-down list containing
    Y, N or N/A

    This is being used on a TabletPC but if I make a mistake or need to change
    back to a blank field I have to invoke the soft keyboard, activate the cell
    and hit backspace then close the soft keyboard - quite a long-winded
    procedure just to change an incorrect choice!

    What I would like to do is add a blank to the list so if I have to revert
    back to a blank I can just use the stylus to choose a blank from the chooser
    list.

    How do I add the option of inputting a blank from the Data Validation List
    bearing in mind I am using the Data Validation Source box for entering my
    choices directly and not specifying a range of cells?

    I have tried adding "" and even a space to no avail.

    Although not a betting man I would wager there is a simple 'fix' for this
    but things are only simple if you know how in the first instance! ;^)

    --
    Thanks & regards,
    -pp-



  2. #2

    Re: Adding a blank in Data Validation List?

    There doesn't seem to be a way.
    I have tried it even by writing a macro code but that too could not be
    made successful as the argument accepts a string with comma delimited
    values.
    Instead of blank,you could use an another value,say 0, and then select
    it and could hide it by checking the zero values box in the
    tool-options-view menu.Else,you could use a different character and
    conditionally format it to white though it may not look good.


  3. #3
    Dave Peterson
    Guest

    Re: Adding a blank in Data Validation List?

    Can you just hit the delete key to clear the contents? (I've never used a
    tablet pc.)

    "Pheasant PluckerŪ" wrote:
    >
    > Hi there,
    >
    > I have a spreadsheet with some cells setup with a drop-down list containing
    > Y, N or N/A
    >
    > This is being used on a TabletPC but if I make a mistake or need to change
    > back to a blank field I have to invoke the soft keyboard, activate the cell
    > and hit backspace then close the soft keyboard - quite a long-winded
    > procedure just to change an incorrect choice!
    >
    > What I would like to do is add a blank to the list so if I have to revert
    > back to a blank I can just use the stylus to choose a blank from the chooser
    > list.
    >
    > How do I add the option of inputting a blank from the Data Validation List
    > bearing in mind I am using the Data Validation Source box for entering my
    > choices directly and not specifying a range of cells?
    >
    > I have tried adding "" and even a space to no avail.
    >
    > Although not a betting man I would wager there is a simple 'fix' for this
    > but things are only simple if you know how in the first instance! ;^)
    >
    > --
    > Thanks & regards,
    > -pp-


    --

    Dave Peterson

  4. #4
    Pheasant Plucker®
    Guest

    Re: Adding a blank in Data Validation List?

    Hello again Dave,

    When it is in Tablet mode the screen turns through 180 degrees and then
    folds down flat and is locked over the keyboard...only the stylus can be
    used for input.

    The idea was for me to make it easy on myself (being lazy?:-) so it would
    actually be much easier to invoke the soft keyboard using the usual 6-tap
    operation rather than taking the TabletPC out of the sleeve, unlocking the
    keyboard, swivelling the keyboard, hitting the delete key and then reversing
    the procedure would be counter-productive to say the least! ;^) <ducking>

    Much quicker while running through the spreadsheet to tap the stylus on the
    List box and select the elusive blank...

    Thanks & kind regards,
    -=pp=-

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > Can you just hit the delete key to clear the contents? (I've never used a
    > tablet pc.)
    >
    > "Pheasant PluckerŪ" wrote:
    > >
    > > Hi there,
    > >
    > > I have a spreadsheet with some cells setup with a drop-down list

    containing
    > > Y, N or N/A
    > >
    > > This is being used on a TabletPC but if I make a mistake or need to

    change
    > > back to a blank field I have to invoke the soft keyboard, activate the

    cell
    > > and hit backspace then close the soft keyboard - quite a long-winded
    > > procedure just to change an incorrect choice!
    > >
    > > What I would like to do is add a blank to the list so if I have to

    revert
    > > back to a blank I can just use the stylus to choose a blank from the

    chooser
    > > list.
    > >
    > > How do I add the option of inputting a blank from the Data Validation

    List
    > > bearing in mind I am using the Data Validation Source box for entering

    my
    > > choices directly and not specifying a range of cells?
    > >
    > > I have tried adding "" and even a space to no avail.
    > >
    > > Although not a betting man I would wager there is a simple 'fix' for

    this
    > > but things are only simple if you know how in the first instance! ;^)
    > >
    > > --
    > > Thanks & regards,
    > > -pp-

    >
    > --
    >
    > Dave Peterson




  5. #5
    Roger Govier
    Guest

    Re: Adding a blank in Data Validation List?

    Hi

    it sounds as though you have your DV set with the list of options
    entered directly into the DV dialogue box with comma separators.
    Instead, set up a list on another sheet (or unused part of same sheet)
    with Y, N, N/A in cells say A1:A3.
    In the DV dialogue, select List, but instead of typing the list enter
    =Sheet2!A1:A4 which will include a Blank for you.

    --
    Regards

    Roger Govier


    "Pheasant PluckerŪ" <[email protected]> wrote in message
    news:[email protected]...
    > Hello again Dave,
    >
    > When it is in Tablet mode the screen turns through 180 degrees and
    > then
    > folds down flat and is locked over the keyboard...only the stylus can
    > be
    > used for input.
    >
    > The idea was for me to make it easy on myself (being lazy?:-) so it
    > would
    > actually be much easier to invoke the soft keyboard using the usual
    > 6-tap
    > operation rather than taking the TabletPC out of the sleeve, unlocking
    > the
    > keyboard, swivelling the keyboard, hitting the delete key and then
    > reversing
    > the procedure would be counter-productive to say the least! ;^)
    > <ducking>
    >
    > Much quicker while running through the spreadsheet to tap the stylus
    > on the
    > List box and select the elusive blank...
    >
    > Thanks & kind regards,
    > -=pp=-
    >
    > "Dave Peterson" <[email protected]> wrote in message
    > news:[email protected]...
    >> Can you just hit the delete key to clear the contents? (I've never
    >> used a
    >> tablet pc.)
    >>
    >> "Pheasant PluckerŪ" wrote:
    >> >
    >> > Hi there,
    >> >
    >> > I have a spreadsheet with some cells setup with a drop-down list

    > containing
    >> > Y, N or N/A
    >> >
    >> > This is being used on a TabletPC but if I make a mistake or need to

    > change
    >> > back to a blank field I have to invoke the soft keyboard, activate
    >> > the

    > cell
    >> > and hit backspace then close the soft keyboard - quite a
    >> > long-winded
    >> > procedure just to change an incorrect choice!
    >> >
    >> > What I would like to do is add a blank to the list so if I have to

    > revert
    >> > back to a blank I can just use the stylus to choose a blank from
    >> > the

    > chooser
    >> > list.
    >> >
    >> > How do I add the option of inputting a blank from the Data
    >> > Validation

    > List
    >> > bearing in mind I am using the Data Validation Source box for
    >> > entering

    > my
    >> > choices directly and not specifying a range of cells?
    >> >
    >> > I have tried adding "" and even a space to no avail.
    >> >
    >> > Although not a betting man I would wager there is a simple 'fix'
    >> > for

    > this
    >> > but things are only simple if you know how in the first instance!
    >> > ;^)
    >> >
    >> > --
    >> > Thanks & regards,
    >> > -pp-

    >>
    >> --
    >>
    >> Dave Peterson

    >
    >




  6. #6
    Pheasant Plucker®
    Guest

    Re: Adding a blank in Data Validation List?

    Hi Roger,

    Thanks for the quick reply...

    You are correct in that I do have the DV set with the list of options
    entered directly into the DV dialogue box with comma separators.

    I would much prefer to leave it this way if at all possible without setting
    up a list on an existing or another sheet?

    Anyway if I add an extra sheet and change the Source then I get an error
    saying "You may not use references to other worksheets or workbooks for Data
    Validation criteria"

    It does work if I put separate entries on each sheet but that way it is a
    lot of work to update if any of the entries change...

    Putting all the info on a separate worksheet would be a good compromise -
    data all in one place, easy to update etc. but for the problem I described
    above.

    Is DV supposed to work across a separate worksheet within the same
    spreadsheet in Excel 2000?

    Thanks & regards,
    -=pp=-


    "Roger Govier" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi
    >
    > it sounds as though you have your DV set with the list of options
    > entered directly into the DV dialogue box with comma separators.
    > Instead, set up a list on another sheet (or unused part of same sheet)
    > with Y, N, N/A in cells say A1:A3.
    > In the DV dialogue, select List, but instead of typing the list enter
    > =Sheet2!A1:A4 which will include a Blank for you.
    >
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "Pheasant PluckerŪ" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hello again Dave,
    > >
    > > When it is in Tablet mode the screen turns through 180 degrees and
    > > then
    > > folds down flat and is locked over the keyboard...only the stylus can
    > > be
    > > used for input.
    > >
    > > The idea was for me to make it easy on myself (being lazy?:-) so it
    > > would
    > > actually be much easier to invoke the soft keyboard using the usual
    > > 6-tap
    > > operation rather than taking the TabletPC out of the sleeve, unlocking
    > > the
    > > keyboard, swivelling the keyboard, hitting the delete key and then
    > > reversing
    > > the procedure would be counter-productive to say the least! ;^)
    > > <ducking>
    > >
    > > Much quicker while running through the spreadsheet to tap the stylus
    > > on the
    > > List box and select the elusive blank...
    > >
    > > Thanks & kind regards,
    > > -=pp=-
    > >
    > > "Dave Peterson" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Can you just hit the delete key to clear the contents? (I've never
    > >> used a
    > >> tablet pc.)
    > >>
    > >> "Pheasant PluckerŪ" wrote:
    > >> >
    > >> > Hi there,
    > >> >
    > >> > I have a spreadsheet with some cells setup with a drop-down list

    > > containing
    > >> > Y, N or N/A
    > >> >
    > >> > This is being used on a TabletPC but if I make a mistake or need to

    > > change
    > >> > back to a blank field I have to invoke the soft keyboard, activate
    > >> > the

    > > cell
    > >> > and hit backspace then close the soft keyboard - quite a
    > >> > long-winded
    > >> > procedure just to change an incorrect choice!
    > >> >
    > >> > What I would like to do is add a blank to the list so if I have to

    > > revert
    > >> > back to a blank I can just use the stylus to choose a blank from
    > >> > the

    > > chooser
    > >> > list.
    > >> >
    > >> > How do I add the option of inputting a blank from the Data
    > >> > Validation

    > > List
    > >> > bearing in mind I am using the Data Validation Source box for
    > >> > entering

    > > my
    > >> > choices directly and not specifying a range of cells?
    > >> >
    > >> > I have tried adding "" and even a space to no avail.
    > >> >
    > >> > Although not a betting man I would wager there is a simple 'fix'
    > >> > for

    > > this
    > >> > but things are only simple if you know how in the first instance!
    > >> > ;^)
    > >> >
    > >> > --
    > >> > Thanks & regards,
    > >> > -pp-
    > >>
    > >> --
    > >>
    > >> Dave Peterson

    > >
    > >

    >
    >




  7. #7
    M. Authement
    Guest

    Re: Adding a blank in Data Validation List?

    If you name the range that contains your list, then refer to the name rather
    than the range, it will work across sheets.

    "Pheasant PluckerŪ" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Roger,
    >
    > Thanks for the quick reply...
    >
    > You are correct in that I do have the DV set with the list of options
    > entered directly into the DV dialogue box with comma separators.
    >
    > I would much prefer to leave it this way if at all possible without
    > setting
    > up a list on an existing or another sheet?
    >
    > Anyway if I add an extra sheet and change the Source then I get an error
    > saying "You may not use references to other worksheets or workbooks for
    > Data
    > Validation criteria"
    >
    > It does work if I put separate entries on each sheet but that way it is a
    > lot of work to update if any of the entries change...
    >
    > Putting all the info on a separate worksheet would be a good compromise -
    > data all in one place, easy to update etc. but for the problem I described
    > above.
    >
    > Is DV supposed to work across a separate worksheet within the same
    > spreadsheet in Excel 2000?
    >
    > Thanks & regards,
    > -=pp=-
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:%[email protected]...
    >> Hi
    >>
    >> it sounds as though you have your DV set with the list of options
    >> entered directly into the DV dialogue box with comma separators.
    >> Instead, set up a list on another sheet (or unused part of same sheet)
    >> with Y, N, N/A in cells say A1:A3.
    >> In the DV dialogue, select List, but instead of typing the list enter
    >> =Sheet2!A1:A4 which will include a Blank for you.
    >>
    >> --
    >> Regards
    >>
    >> Roger Govier
    >>
    >>
    >> "Pheasant PluckerŪ" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Hello again Dave,
    >> >
    >> > When it is in Tablet mode the screen turns through 180 degrees and
    >> > then
    >> > folds down flat and is locked over the keyboard...only the stylus can
    >> > be
    >> > used for input.
    >> >
    >> > The idea was for me to make it easy on myself (being lazy?:-) so it
    >> > would
    >> > actually be much easier to invoke the soft keyboard using the usual
    >> > 6-tap
    >> > operation rather than taking the TabletPC out of the sleeve, unlocking
    >> > the
    >> > keyboard, swivelling the keyboard, hitting the delete key and then
    >> > reversing
    >> > the procedure would be counter-productive to say the least! ;^)
    >> > <ducking>
    >> >
    >> > Much quicker while running through the spreadsheet to tap the stylus
    >> > on the
    >> > List box and select the elusive blank...
    >> >
    >> > Thanks & kind regards,
    >> > -=pp=-
    >> >
    >> > "Dave Peterson" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> Can you just hit the delete key to clear the contents? (I've never
    >> >> used a
    >> >> tablet pc.)
    >> >>
    >> >> "Pheasant PluckerŪ" wrote:
    >> >> >
    >> >> > Hi there,
    >> >> >
    >> >> > I have a spreadsheet with some cells setup with a drop-down list
    >> > containing
    >> >> > Y, N or N/A
    >> >> >
    >> >> > This is being used on a TabletPC but if I make a mistake or need to
    >> > change
    >> >> > back to a blank field I have to invoke the soft keyboard, activate
    >> >> > the
    >> > cell
    >> >> > and hit backspace then close the soft keyboard - quite a
    >> >> > long-winded
    >> >> > procedure just to change an incorrect choice!
    >> >> >
    >> >> > What I would like to do is add a blank to the list so if I have to
    >> > revert
    >> >> > back to a blank I can just use the stylus to choose a blank from
    >> >> > the
    >> > chooser
    >> >> > list.
    >> >> >
    >> >> > How do I add the option of inputting a blank from the Data
    >> >> > Validation
    >> > List
    >> >> > bearing in mind I am using the Data Validation Source box for
    >> >> > entering
    >> > my
    >> >> > choices directly and not specifying a range of cells?
    >> >> >
    >> >> > I have tried adding "" and even a space to no avail.
    >> >> >
    >> >> > Although not a betting man I would wager there is a simple 'fix'
    >> >> > for
    >> > this
    >> >> > but things are only simple if you know how in the first instance!
    >> >> > ;^)
    >> >> >
    >> >> > --
    >> >> > Thanks & regards,
    >> >> > -pp-
    >> >>
    >> >> --
    >> >>
    >> >> Dave Peterson
    >> >
    >> >

    >>
    >>

    >
    >




  8. #8
    Roger Govier
    Guest

    Re: Adding a blank in Data Validation List?

    Hi

    Using DV lists from other sheets in the same workbook is not normally a
    problem. I use it all the time in XL2000 and XL2003.
    Using the lists from other Workbooks can be a problem, but there is
    usually a way around it.

    I played about with achieving your requirement with a type list, and I
    think the following will be suitable for your requirement, but note that
    it will make the blank cell a text format (which I am assuming is not a
    problem because of your other inputs).
    Make the DV list Y,N,N/A,',
    The last one is the single quote. It will appear on the dropdown as the
    single quote mark, but when selected the cell will be blank.

    --
    Regards

    Roger Govier


    "Pheasant PluckerŪ" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Roger,
    >
    > Thanks for the quick reply...
    >
    > You are correct in that I do have the DV set with the list of options
    > entered directly into the DV dialogue box with comma separators.
    >
    > I would much prefer to leave it this way if at all possible without
    > setting
    > up a list on an existing or another sheet?
    >
    > Anyway if I add an extra sheet and change the Source then I get an
    > error
    > saying "You may not use references to other worksheets or workbooks
    > for Data
    > Validation criteria"
    >
    > It does work if I put separate entries on each sheet but that way it
    > is a
    > lot of work to update if any of the entries change...
    >
    > Putting all the info on a separate worksheet would be a good
    > compromise -
    > data all in one place, easy to update etc. but for the problem I
    > described
    > above.
    >
    > Is DV supposed to work across a separate worksheet within the same
    > spreadsheet in Excel 2000?
    >
    > Thanks & regards,
    > -=pp=-
    >
    >
    > "Roger Govier" <[email protected]> wrote in message
    > news:%[email protected]...
    >> Hi
    >>
    >> it sounds as though you have your DV set with the list of options
    >> entered directly into the DV dialogue box with comma separators.
    >> Instead, set up a list on another sheet (or unused part of same
    >> sheet)
    >> with Y, N, N/A in cells say A1:A3.
    >> In the DV dialogue, select List, but instead of typing the list enter
    >> =Sheet2!A1:A4 which will include a Blank for you.
    >>
    >> --
    >> Regards
    >>
    >> Roger Govier
    >>
    >>
    >> "Pheasant PluckerŪ" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Hello again Dave,
    >> >
    >> > When it is in Tablet mode the screen turns through 180 degrees and
    >> > then
    >> > folds down flat and is locked over the keyboard...only the stylus
    >> > can
    >> > be
    >> > used for input.
    >> >
    >> > The idea was for me to make it easy on myself (being lazy?:-) so it
    >> > would
    >> > actually be much easier to invoke the soft keyboard using the usual
    >> > 6-tap
    >> > operation rather than taking the TabletPC out of the sleeve,
    >> > unlocking
    >> > the
    >> > keyboard, swivelling the keyboard, hitting the delete key and then
    >> > reversing
    >> > the procedure would be counter-productive to say the least! ;^)
    >> > <ducking>
    >> >
    >> > Much quicker while running through the spreadsheet to tap the
    >> > stylus
    >> > on the
    >> > List box and select the elusive blank...
    >> >
    >> > Thanks & kind regards,
    >> > -=pp=-
    >> >
    >> > "Dave Peterson" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> Can you just hit the delete key to clear the contents? (I've
    >> >> never
    >> >> used a
    >> >> tablet pc.)
    >> >>
    >> >> "Pheasant PluckerŪ" wrote:
    >> >> >
    >> >> > Hi there,
    >> >> >
    >> >> > I have a spreadsheet with some cells setup with a drop-down list
    >> > containing
    >> >> > Y, N or N/A
    >> >> >
    >> >> > This is being used on a TabletPC but if I make a mistake or need
    >> >> > to
    >> > change
    >> >> > back to a blank field I have to invoke the soft keyboard,
    >> >> > activate
    >> >> > the
    >> > cell
    >> >> > and hit backspace then close the soft keyboard - quite a
    >> >> > long-winded
    >> >> > procedure just to change an incorrect choice!
    >> >> >
    >> >> > What I would like to do is add a blank to the list so if I have
    >> >> > to
    >> > revert
    >> >> > back to a blank I can just use the stylus to choose a blank from
    >> >> > the
    >> > chooser
    >> >> > list.
    >> >> >
    >> >> > How do I add the option of inputting a blank from the Data
    >> >> > Validation
    >> > List
    >> >> > bearing in mind I am using the Data Validation Source box for
    >> >> > entering
    >> > my
    >> >> > choices directly and not specifying a range of cells?
    >> >> >
    >> >> > I have tried adding "" and even a space to no avail.
    >> >> >
    >> >> > Although not a betting man I would wager there is a simple 'fix'
    >> >> > for
    >> > this
    >> >> > but things are only simple if you know how in the first
    >> >> > instance!
    >> >> > ;^)
    >> >> >
    >> >> > --
    >> >> > Thanks & regards,
    >> >> > -pp-
    >> >>
    >> >> --
    >> >>
    >> >> Dave Peterson
    >> >
    >> >

    >>
    >>

    >
    >




  9. #9
    Pheasant Plucker®
    Guest

    Re: Adding a blank in Data Validation List?

    Thanks for the quick reply...please don't forget that you are dealing with a
    complete muppet when it comes to Excel so...

    How do I name a range?

    <ducking>

    Thanks & regards,
    -=Glyn=-

    "M. Authement" <[email protected]> wrote in message
    news:[email protected]...
    > If you name the range that contains your list, then refer to the name

    rather
    > than the range, it will work across sheets.
    >
    > "Pheasant PluckerŪ" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi Roger,
    > >
    > > Thanks for the quick reply...
    > >
    > > You are correct in that I do have the DV set with the list of options
    > > entered directly into the DV dialogue box with comma separators.
    > >
    > > I would much prefer to leave it this way if at all possible without
    > > setting
    > > up a list on an existing or another sheet?
    > >
    > > Anyway if I add an extra sheet and change the Source then I get an error
    > > saying "You may not use references to other worksheets or workbooks for
    > > Data
    > > Validation criteria"
    > >
    > > It does work if I put separate entries on each sheet but that way it is

    a
    > > lot of work to update if any of the entries change...
    > >
    > > Putting all the info on a separate worksheet would be a good

    compromise -
    > > data all in one place, easy to update etc. but for the problem I

    described
    > > above.
    > >
    > > Is DV supposed to work across a separate worksheet within the same
    > > spreadsheet in Excel 2000?
    > >
    > > Thanks & regards,
    > > -=pp=-
    > >
    > >
    > > "Roger Govier" <[email protected]> wrote in message
    > > news:%[email protected]...
    > >> Hi
    > >>
    > >> it sounds as though you have your DV set with the list of options
    > >> entered directly into the DV dialogue box with comma separators.
    > >> Instead, set up a list on another sheet (or unused part of same sheet)
    > >> with Y, N, N/A in cells say A1:A3.
    > >> In the DV dialogue, select List, but instead of typing the list enter
    > >> =Sheet2!A1:A4 which will include a Blank for you.
    > >>
    > >> --
    > >> Regards
    > >>
    > >> Roger Govier
    > >>
    > >>
    > >> "Pheasant PluckerŪ" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hello again Dave,
    > >> >
    > >> > When it is in Tablet mode the screen turns through 180 degrees and
    > >> > then
    > >> > folds down flat and is locked over the keyboard...only the stylus can
    > >> > be
    > >> > used for input.
    > >> >
    > >> > The idea was for me to make it easy on myself (being lazy?:-) so it
    > >> > would
    > >> > actually be much easier to invoke the soft keyboard using the usual
    > >> > 6-tap
    > >> > operation rather than taking the TabletPC out of the sleeve,

    unlocking
    > >> > the
    > >> > keyboard, swivelling the keyboard, hitting the delete key and then
    > >> > reversing
    > >> > the procedure would be counter-productive to say the least! ;^)
    > >> > <ducking>
    > >> >
    > >> > Much quicker while running through the spreadsheet to tap the stylus
    > >> > on the
    > >> > List box and select the elusive blank...
    > >> >
    > >> > Thanks & kind regards,
    > >> > -=pp=-
    > >> >
    > >> > "Dave Peterson" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> Can you just hit the delete key to clear the contents? (I've never
    > >> >> used a
    > >> >> tablet pc.)
    > >> >>
    > >> >> "Pheasant PluckerŪ" wrote:
    > >> >> >
    > >> >> > Hi there,
    > >> >> >
    > >> >> > I have a spreadsheet with some cells setup with a drop-down list
    > >> > containing
    > >> >> > Y, N or N/A
    > >> >> >
    > >> >> > This is being used on a TabletPC but if I make a mistake or need

    to
    > >> > change
    > >> >> > back to a blank field I have to invoke the soft keyboard, activate
    > >> >> > the
    > >> > cell
    > >> >> > and hit backspace then close the soft keyboard - quite a
    > >> >> > long-winded
    > >> >> > procedure just to change an incorrect choice!
    > >> >> >
    > >> >> > What I would like to do is add a blank to the list so if I have to
    > >> > revert
    > >> >> > back to a blank I can just use the stylus to choose a blank from
    > >> >> > the
    > >> > chooser
    > >> >> > list.
    > >> >> >
    > >> >> > How do I add the option of inputting a blank from the Data
    > >> >> > Validation
    > >> > List
    > >> >> > bearing in mind I am using the Data Validation Source box for
    > >> >> > entering
    > >> > my
    > >> >> > choices directly and not specifying a range of cells?
    > >> >> >
    > >> >> > I have tried adding "" and even a space to no avail.
    > >> >> >
    > >> >> > Although not a betting man I would wager there is a simple 'fix'
    > >> >> > for
    > >> > this
    > >> >> > but things are only simple if you know how in the first instance!
    > >> >> > ;^)
    > >> >> >
    > >> >> > --
    > >> >> > Thanks & regards,
    > >> >> > -pp-
    > >> >>
    > >> >> --
    > >> >>
    > >> >> Dave Peterson
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  10. #10
    Dave Peterson
    Guest

    Re: Adding a blank in Data Validation List?

    Debra Dalgleish has a nice intro to Data|Validation:
    http://www.contextures.com/xlDataVal01.html

    She includes a section on naming ranges:
    http://www.contextures.com/xlDataVal01.html#Name

    "Pheasant PluckerŪ" wrote:
    >
    > Thanks for the quick reply...please don't forget that you are dealing with a
    > complete muppet when it comes to Excel so...
    >
    > How do I name a range?
    >
    > <ducking>
    >
    > Thanks & regards,
    > -=Glyn=-
    >
    > "M. Authement" <[email protected]> wrote in message
    > news:[email protected]...
    > > If you name the range that contains your list, then refer to the name

    > rather
    > > than the range, it will work across sheets.
    > >
    > > "Pheasant PluckerŪ" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Hi Roger,
    > > >
    > > > Thanks for the quick reply...
    > > >
    > > > You are correct in that I do have the DV set with the list of options
    > > > entered directly into the DV dialogue box with comma separators.
    > > >
    > > > I would much prefer to leave it this way if at all possible without
    > > > setting
    > > > up a list on an existing or another sheet?
    > > >
    > > > Anyway if I add an extra sheet and change the Source then I get an error
    > > > saying "You may not use references to other worksheets or workbooks for
    > > > Data
    > > > Validation criteria"
    > > >
    > > > It does work if I put separate entries on each sheet but that way it is

    > a
    > > > lot of work to update if any of the entries change...
    > > >
    > > > Putting all the info on a separate worksheet would be a good

    > compromise -
    > > > data all in one place, easy to update etc. but for the problem I

    > described
    > > > above.
    > > >
    > > > Is DV supposed to work across a separate worksheet within the same
    > > > spreadsheet in Excel 2000?
    > > >
    > > > Thanks & regards,
    > > > -=pp=-
    > > >
    > > >
    > > > "Roger Govier" <[email protected]> wrote in message
    > > > news:%[email protected]...
    > > >> Hi
    > > >>
    > > >> it sounds as though you have your DV set with the list of options
    > > >> entered directly into the DV dialogue box with comma separators.
    > > >> Instead, set up a list on another sheet (or unused part of same sheet)
    > > >> with Y, N, N/A in cells say A1:A3.
    > > >> In the DV dialogue, select List, but instead of typing the list enter
    > > >> =Sheet2!A1:A4 which will include a Blank for you.
    > > >>
    > > >> --
    > > >> Regards
    > > >>
    > > >> Roger Govier
    > > >>
    > > >>
    > > >> "Pheasant PluckerŪ" <[email protected]> wrote in message
    > > >> news:[email protected]...
    > > >> > Hello again Dave,
    > > >> >
    > > >> > When it is in Tablet mode the screen turns through 180 degrees and
    > > >> > then
    > > >> > folds down flat and is locked over the keyboard...only the stylus can
    > > >> > be
    > > >> > used for input.
    > > >> >
    > > >> > The idea was for me to make it easy on myself (being lazy?:-) so it
    > > >> > would
    > > >> > actually be much easier to invoke the soft keyboard using the usual
    > > >> > 6-tap
    > > >> > operation rather than taking the TabletPC out of the sleeve,

    > unlocking
    > > >> > the
    > > >> > keyboard, swivelling the keyboard, hitting the delete key and then
    > > >> > reversing
    > > >> > the procedure would be counter-productive to say the least! ;^)
    > > >> > <ducking>
    > > >> >
    > > >> > Much quicker while running through the spreadsheet to tap the stylus
    > > >> > on the
    > > >> > List box and select the elusive blank...
    > > >> >
    > > >> > Thanks & kind regards,
    > > >> > -=pp=-
    > > >> >
    > > >> > "Dave Peterson" <[email protected]> wrote in message
    > > >> > news:[email protected]...
    > > >> >> Can you just hit the delete key to clear the contents? (I've never
    > > >> >> used a
    > > >> >> tablet pc.)
    > > >> >>
    > > >> >> "Pheasant PluckerŪ" wrote:
    > > >> >> >
    > > >> >> > Hi there,
    > > >> >> >
    > > >> >> > I have a spreadsheet with some cells setup with a drop-down list
    > > >> > containing
    > > >> >> > Y, N or N/A
    > > >> >> >
    > > >> >> > This is being used on a TabletPC but if I make a mistake or need

    > to
    > > >> > change
    > > >> >> > back to a blank field I have to invoke the soft keyboard, activate
    > > >> >> > the
    > > >> > cell
    > > >> >> > and hit backspace then close the soft keyboard - quite a
    > > >> >> > long-winded
    > > >> >> > procedure just to change an incorrect choice!
    > > >> >> >
    > > >> >> > What I would like to do is add a blank to the list so if I have to
    > > >> > revert
    > > >> >> > back to a blank I can just use the stylus to choose a blank from
    > > >> >> > the
    > > >> > chooser
    > > >> >> > list.
    > > >> >> >
    > > >> >> > How do I add the option of inputting a blank from the Data
    > > >> >> > Validation
    > > >> > List
    > > >> >> > bearing in mind I am using the Data Validation Source box for
    > > >> >> > entering
    > > >> > my
    > > >> >> > choices directly and not specifying a range of cells?
    > > >> >> >
    > > >> >> > I have tried adding "" and even a space to no avail.
    > > >> >> >
    > > >> >> > Although not a betting man I would wager there is a simple 'fix'
    > > >> >> > for
    > > >> > this
    > > >> >> > but things are only simple if you know how in the first instance!
    > > >> >> > ;^)
    > > >> >> >
    > > >> >> > --
    > > >> >> > Thanks & regards,
    > > >> >> > -pp-
    > > >> >>
    > > >> >> --
    > > >> >>
    > > >> >> Dave Peterson
    > > >> >
    > > >> >
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >


    --

    Dave Peterson

  11. #11
    Pheasant Plucker®
    Guest

    Re: Adding a blank in Data Validation List?

    Once more to the rescue :-)

    Great reading there...thanks Dave!

    Kind regards,
    -=pp=-


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > Debra Dalgleish has a nice intro to Data|Validation:
    > http://www.contextures.com/xlDataVal01.html
    >
    > She includes a section on naming ranges:
    > http://www.contextures.com/xlDataVal01.html#Name
    >
    > "Pheasant PluckerŪ" wrote:
    > >
    > > Thanks for the quick reply...please don't forget that you are dealing

    with a
    > > complete muppet when it comes to Excel so...
    > >
    > > How do I name a range?
    > >
    > > <ducking>
    > >
    > > Thanks & regards,
    > > -=Glyn=-
    > >
    > > "M. Authement" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > If you name the range that contains your list, then refer to the name

    > > rather
    > > > than the range, it will work across sheets.
    > > >
    > > > "Pheasant PluckerŪ" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > Hi Roger,
    > > > >
    > > > > Thanks for the quick reply...
    > > > >
    > > > > You are correct in that I do have the DV set with the list of

    options
    > > > > entered directly into the DV dialogue box with comma separators.
    > > > >
    > > > > I would much prefer to leave it this way if at all possible without
    > > > > setting
    > > > > up a list on an existing or another sheet?
    > > > >
    > > > > Anyway if I add an extra sheet and change the Source then I get an

    error
    > > > > saying "You may not use references to other worksheets or workbooks

    for
    > > > > Data
    > > > > Validation criteria"
    > > > >
    > > > > It does work if I put separate entries on each sheet but that way it

    is
    > > a
    > > > > lot of work to update if any of the entries change...
    > > > >
    > > > > Putting all the info on a separate worksheet would be a good

    > > compromise -
    > > > > data all in one place, easy to update etc. but for the problem I

    > > described
    > > > > above.
    > > > >
    > > > > Is DV supposed to work across a separate worksheet within the same
    > > > > spreadsheet in Excel 2000?
    > > > >
    > > > > Thanks & regards,
    > > > > -=pp=-
    > > > >
    > > > >
    > > > > "Roger Govier" <[email protected]> wrote in message
    > > > > news:%[email protected]...
    > > > >> Hi
    > > > >>
    > > > >> it sounds as though you have your DV set with the list of options
    > > > >> entered directly into the DV dialogue box with comma separators.
    > > > >> Instead, set up a list on another sheet (or unused part of same

    sheet)
    > > > >> with Y, N, N/A in cells say A1:A3.
    > > > >> In the DV dialogue, select List, but instead of typing the list

    enter
    > > > >> =Sheet2!A1:A4 which will include a Blank for you.
    > > > >>
    > > > >> --
    > > > >> Regards
    > > > >>
    > > > >> Roger Govier
    > > > >>
    > > > >>
    > > > >> "Pheasant PluckerŪ" <[email protected]> wrote in message
    > > > >> news:[email protected]...
    > > > >> > Hello again Dave,
    > > > >> >
    > > > >> > When it is in Tablet mode the screen turns through 180 degrees

    and
    > > > >> > then
    > > > >> > folds down flat and is locked over the keyboard...only the stylus

    can
    > > > >> > be
    > > > >> > used for input.
    > > > >> >
    > > > >> > The idea was for me to make it easy on myself (being lazy?:-) so

    it
    > > > >> > would
    > > > >> > actually be much easier to invoke the soft keyboard using the

    usual
    > > > >> > 6-tap
    > > > >> > operation rather than taking the TabletPC out of the sleeve,

    > > unlocking
    > > > >> > the
    > > > >> > keyboard, swivelling the keyboard, hitting the delete key and

    then
    > > > >> > reversing
    > > > >> > the procedure would be counter-productive to say the least! ;^)
    > > > >> > <ducking>
    > > > >> >
    > > > >> > Much quicker while running through the spreadsheet to tap the

    stylus
    > > > >> > on the
    > > > >> > List box and select the elusive blank...
    > > > >> >
    > > > >> > Thanks & kind regards,
    > > > >> > -=pp=-
    > > > >> >
    > > > >> > "Dave Peterson" <[email protected]> wrote in message
    > > > >> > news:[email protected]...
    > > > >> >> Can you just hit the delete key to clear the contents? (I've

    never
    > > > >> >> used a
    > > > >> >> tablet pc.)
    > > > >> >>
    > > > >> >> "Pheasant PluckerŪ" wrote:
    > > > >> >> >
    > > > >> >> > Hi there,
    > > > >> >> >
    > > > >> >> > I have a spreadsheet with some cells setup with a drop-down

    list
    > > > >> > containing
    > > > >> >> > Y, N or N/A
    > > > >> >> >
    > > > >> >> > This is being used on a TabletPC but if I make a mistake or

    need
    > > to
    > > > >> > change
    > > > >> >> > back to a blank field I have to invoke the soft keyboard,

    activate
    > > > >> >> > the
    > > > >> > cell
    > > > >> >> > and hit backspace then close the soft keyboard - quite a
    > > > >> >> > long-winded
    > > > >> >> > procedure just to change an incorrect choice!
    > > > >> >> >
    > > > >> >> > What I would like to do is add a blank to the list so if I

    have to
    > > > >> > revert
    > > > >> >> > back to a blank I can just use the stylus to choose a blank

    from
    > > > >> >> > the
    > > > >> > chooser
    > > > >> >> > list.
    > > > >> >> >
    > > > >> >> > How do I add the option of inputting a blank from the Data
    > > > >> >> > Validation
    > > > >> > List
    > > > >> >> > bearing in mind I am using the Data Validation Source box for
    > > > >> >> > entering
    > > > >> > my
    > > > >> >> > choices directly and not specifying a range of cells?
    > > > >> >> >
    > > > >> >> > I have tried adding "" and even a space to no avail.
    > > > >> >> >
    > > > >> >> > Although not a betting man I would wager there is a simple

    'fix'
    > > > >> >> > for
    > > > >> > this
    > > > >> >> > but things are only simple if you know how in the first

    instance!
    > > > >> >> > ;^)
    > > > >> >> >
    > > > >> >> > --
    > > > >> >> > Thanks & regards,
    > > > >> >> > -pp-
    > > > >> >>
    > > > >> >> --
    > > > >> >>
    > > > >> >> Dave Peterson
    > > > >> >
    > > > >> >
    > > > >>
    > > > >>
    > > > >
    > > > >
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson




  12. #12
    Registered User
    Join Date
    05-22-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Adding a blank in Data Validation List?

    OMG! Brilliant! using just ' works!

  13. #13
    Registered User
    Join Date
    02-27-2015
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    1

    Thumbs up Re: Adding a blank in Data Validation List?

    I FIGURED IT OUT .... I'm excited because I'm usually coming here with questions, not answers.

    In the FORMULA input box, I entered {blank}{dash}{dash}{blank}, Yes, No. Which looks like this: -- ,Yes, No

    It then gave me a blank as the first entry in the pull down list.

  14. #14
    Registered User
    Join Date
    11-29-2013
    Location
    China
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Adding a blank in Data Validation List?

    Interesting, I was just looking for a way to do this too.

    The last tip about " -- " (quote marks excluded) works great, but I was curious and did a bit more testing, and found that other strings worked too, for example:

    " -- "
    "--"
    "----------"
    " ---- "
    " - - "
    "-empty-"
    "- -- blank ## -"

    All of the above strings will produce a blank when picked from the data validation drop-down list. The only difference is the text you see in the drop-down.

    After experimenting with different strings, my conclusion is that any string that satisfies the following two conditions will produce a blank:

    1) The first non-blank character of the string is "-"
    2) The last non-blank character of the string is also "-"

    You can have any characters between the starting and ending "-", allowing you to have a list entry that says "- Empty -", and so on.
    Last edited by Scoox; 04-26-2016 at 11:24 PM.

  15. #15
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Re: Adding a blank in Data Validation List?

    I really liked the idea of adding a single quote (') to the list, but I'm afraid my users will see it in the drop-down and not realize what it's supposed to represent.

    I'm going to try this: in the list I'll enter "Y, N, (Blank)". Then a very little VBA code: in the WorksheetChange event I enter:

    Please Login or Register  to view this content.
    So the user will see "(Blank)" in the drop-down, but when he chooses it, the cell will actually be blank.

  16. #16
    Registered User
    Join Date
    10-31-2017
    Location
    CA
    MS-Off Ver
    20xx
    Posts
    1

    Re: Adding a blank in Data Validation List?

    If you want to include a space in a situation like this, you can hold <ALT> and type 255 on your tenkey pad. As soon as you let go of the <ALT> the space will be inserted and in your list, a blank will be available. It will really be a space, but the casual observer won't notice.

+ 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