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Ū" <pheasant@plucker.not> wrote in message
news:Ok64pgdSGHA.4452@TK2MSFTNGP12.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23Jye$fcSGHA.5884@TK2MSFTNGP14.phx.gbl...
>> 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Ū" <pheasant@plucker.not> wrote in message
>> news:Op7MTYcSGHA.5656@TK2MSFTNGP11.phx.gbl...
>> > 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" <petersod@verizonXSPAM.net> wrote in message
>> > news:441AA730.F1C378C9@verizonXSPAM.net...
>> >> 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
>> >
>> >
>>
>>
>
>