Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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®" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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®" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a blank in Data Validation List?
Once more to the rescue :-)
Great reading there...thanks Dave! Kind regards, -=pp=- "Dave Peterson" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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®" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List Length | Excel Worksheet Functions | |||
Data Validation and Blanks in List | Excel Worksheet Functions | |||
Handling "Blank Entries" through Data Validation | Excel Discussion (Misc queries) | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel | |||
Returning a blank for validation list | Excel Worksheet Functions |