How can I create a data validation for a single text entry (i.e. to allow
leaving the cell blank or entering the word "Yes") without having to create a
list of the single entry (i.e. Yes). The other options in data validation
related to numbers, not text.
Thank you
--
MZ

Hi,
I assume I enter the word Yes in cell B1 then position in the cell you want
to apply data validation, go to data validation, custom and enter there the
formula as follow

=OR(A1=B1,A1="")

the above formula is validating data entered in A1

"MZ" wrote:

> How can I create a data validation for a single text entry (i.e. to allow
> leaving the cell blank or entering the word "Yes") without having to create a
> list of the single entry (i.e. Yes). The other options in data validation
> related to numbers, not text.
> Thank you
> --
> MZ

Assuming your input cell is F10, In the data validation box -- choose Custom
and in the formula box put:

=OR(ISBLANK(F10)=TRUE,F10="Yes")


--
hope to help,
cm


"MZ" wrote:

> How can I create a data validation for a single text entry (i.e. to allow
> leaving the cell blank or entering the word "Yes") without having to create a
> list of the single entry (i.e. Yes). The other options in data validation
> related to numbers, not text.
> Thank you
> --
> MZ

Assume you want to allow only the entry Yes in cell A1 (or leave it empty).

Select cell A1
Goto the menu Data>Validation
Allow: Custom
Formula: =A1="yes"
OK

--
Biff
Microsoft Excel MVP


"MZ" <(E-Mail Removed)> wrote in message
news:90D9E3D7-3A6E-4213-94F8-(E-Mail Removed)...
> How can I create a data validation for a single text entry (i.e. to allow
> leaving the cell blank or entering the word "Yes") without having to
> create a
> list of the single entry (i.e. Yes). The other options in data validation
> related to numbers, not text.
> Thank you
> --
> MZ

Thank you T. Valko,
I tried your suggestions and typed: =AF="Yes" (AF is the cell address)
but received a pop-up error in the Validation box that states: A named range
you specified cannot be found
--
MZ


"T. Valko" wrote:

> Assume you want to allow only the entry Yes in cell A1 (or leave it empty).
>
> Select cell A1
> Goto the menu Data>Validation
> Allow: Custom
> Formula: =A1="yes"
> OK
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "MZ" <(E-Mail Removed)> wrote in message
> news:90D9E3D7-3A6E-4213-94F8-(E-Mail Removed)...
> > How can I create a data validation for a single text entry (i.e. to allow
> > leaving the cell blank or entering the word "Yes") without having to
> > create a
> > list of the single entry (i.e. Yes). The other options in data validation
> > related to numbers, not text.
> > Thank you
> > --
> > MZ
>
>
> .
>

>I tried your suggestions and typed: =AF="Yes"
>(AF is the cell address)

Ok, so what's the row number of the cell address? Is it AF10? AF25? You need
to include the row number.

--
Biff
Microsoft Excel MVP


"MZ" <(E-Mail Removed)> wrote in message
news:C49DE148-281B-4254-867D-(E-Mail Removed)...
> Thank you T. Valko,
> I tried your suggestions and typed: =AF="Yes" (AF is the cell address)
> but received a pop-up error in the Validation box that states: A named
> range
> you specified cannot be found
> --
> MZ
>
>
> "T. Valko" wrote:
>
>> Assume you want to allow only the entry Yes in cell A1 (or leave it
>> empty).
>>
>> Select cell A1
>> Goto the menu Data>Validation
>> Allow: Custom
>> Formula: =A1="yes"
>> OK
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "MZ" <(E-Mail Removed)> wrote in message
>> news:90D9E3D7-3A6E-4213-94F8-(E-Mail Removed)...
>> > How can I create a data validation for a single text entry (i.e. to
>> > allow
>> > leaving the cell blank or entering the word "Yes") without having to
>> > create a
>> > list of the single entry (i.e. Yes). The other options in data
>> > validation
>> > related to numbers, not text.
>> > Thank you
>> > --
>> > MZ
>>
>>
>> .
>>

Thank you for correcting my error in identifying the cell; it works fine now
--
MZ


"T. Valko" wrote:

> >I tried your suggestions and typed: =AF="Yes"
> >(AF is the cell address)
>
> Ok, so what's the row number of the cell address? Is it AF10? AF25? You need
> to include the row number.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "MZ" <(E-Mail Removed)> wrote in message
> news:C49DE148-281B-4254-867D-(E-Mail Removed)...
> > Thank you T. Valko,
> > I tried your suggestions and typed: =AF="Yes" (AF is the cell address)
> > but received a pop-up error in the Validation box that states: A named
> > range
> > you specified cannot be found
> > --
> > MZ
> >
> >
> > "T. Valko" wrote:
> >
> >> Assume you want to allow only the entry Yes in cell A1 (or leave it
> >> empty).
> >>
> >> Select cell A1
> >> Goto the menu Data>Validation
> >> Allow: Custom
> >> Formula: =A1="yes"
> >> OK
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "MZ" <(E-Mail Removed)> wrote in message
> >> news:90D9E3D7-3A6E-4213-94F8-(E-Mail Removed)...
> >> > How can I create a data validation for a single text entry (i.e. to
> >> > allow
> >> > leaving the cell blank or entering the word "Yes") without having to
> >> > create a
> >> > list of the single entry (i.e. Yes). The other options in data
> >> > validation
> >> > related to numbers, not text.
> >> > Thank you
> >> > --
> >> > MZ
> >>
> >>
> >> .
> >>
>
>
> .
>

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MZ" <(E-Mail Removed)> wrote in message
news:9FF22C6D-D259-44AF-AAB7-(E-Mail Removed)...
> Thank you for correcting my error in identifying the cell; it works fine
> now
> --
> MZ
>
>
> "T. Valko" wrote:
>
>> >I tried your suggestions and typed: =AF="Yes"
>> >(AF is the cell address)
>>
>> Ok, so what's the row number of the cell address? Is it AF10? AF25? You
>> need
>> to include the row number.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "MZ" <(E-Mail Removed)> wrote in message
>> news:C49DE148-281B-4254-867D-(E-Mail Removed)...
>> > Thank you T. Valko,
>> > I tried your suggestions and typed: =AF="Yes" (AF is the cell address)
>> > but received a pop-up error in the Validation box that states: A named
>> > range
>> > you specified cannot be found
>> > --
>> > MZ
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Assume you want to allow only the entry Yes in cell A1 (or leave it
>> >> empty).
>> >>
>> >> Select cell A1
>> >> Goto the menu Data>Validation
>> >> Allow: Custom
>> >> Formula: =A1="yes"
>> >> OK
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "MZ" <(E-Mail Removed)> wrote in message
>> >> news:90D9E3D7-3A6E-4213-94F8-(E-Mail Removed)...
>> >> > How can I create a data validation for a single text entry (i.e. to
>> >> > allow
>> >> > leaving the cell blank or entering the word "Yes") without having to
>> >> > create a
>> >> > list of the single entry (i.e. Yes). The other options in data
>> >> > validation
>> >> > related to numbers, not text.
>> >> > Thank you
>> >> > --
>> >> > MZ
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Another option is to select Allow: List
In the Source box, type: Yes
Remove the check mark from the box for In-cell drop down

Note: Entries will have to exactly match what you type in the Source
box, so YES or yes would not be allowed, only Yes

If you want to allow other variations, you could add them in the Source
Box, e.g.: Yes,YES,yes


MZ wrote:
> How can I create a data validation for a single text entry (i.e. to allow
> leaving the cell blank or entering the word "Yes") without having to create a
> list of the single entry (i.e. Yes). The other options in data validation
> related to numbers, not text.
> Thank you


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com