How to setup a 'chooser' box/cell for Y or No?

[Pheasant Plucker®]

Sorry...don't quite know how to describe what I want to do...any telepaths
out there? ;^)

In a spreadsheet I have a need for a cell to display Y or N.

Rather than type in Y or No I would like to be able to select either option
by clicking on the cell and choosing Y or N from a small menu.

I have cut & pasted a similar cell from another spreadsheet so now when I
click in the cell there is the grey square with a black arrow displayed at
the right hand side of the cell.

On the spreadsheet I copied from there was this choice of Y or N but this
did not seem to be copied over to my new spreadsheet.

There did not appear to be a formula to the cell either.

What is the proper name for what I am trying to do and how do I get the
auto-requestor cell to display either Y or N please?

Also is there a way to define a number value in the adjacent cell if N is
selected & displayed?

For example for some entries where N is displayed I need to assign a value
of 1 in the adjacent cell and for others I need to assign a value of 2, 3 4,
or 5.

Is this even possible?

 

[RagDyeR]

You can easily create these drop-down boxes using "Data Validation".

Check out the many pages Debra Dalgleish has on her web site concerning this
subject, starting with:

http://www.contextures.com/xlDataVal01.html

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Sorry...don't quite know how to describe what I want to do...any telepaths
out there? ;^)

In a spreadsheet I have a need for a cell to display Y or N.

Rather than type in Y or No I would like to be able to select either option
by clicking on the cell and choosing Y or N from a small menu.

I have cut & pasted a similar cell from another spreadsheet so now when I
click in the cell there is the grey square with a black arrow displayed at
the right hand side of the cell.

On the spreadsheet I copied from there was this choice of Y or N but this
did not seem to be copied over to my new spreadsheet.

There did not appear to be a formula to the cell either.

What is the proper name for what I am trying to do and how do I get the
auto-requestor cell to display either Y or N please?

Also is there a way to define a number value in the adjacent cell if N is
selected & displayed?

For example for some entries where N is displayed I need to assign a value
of 1 in the adjacent cell and for others I need to assign a value of 2, 3 4,
or 5.

Is this even possible?

 

[Guest]

See if this satisfies your requirements.....

Select the input cells (I'll assume A2:A10).
Data>Validation>
Allow: List
Source: Y,N
Click the [OK] button

Select B2
B2: =IF(A2="N",1,"")
Copy that formula down as far as needed, replacing the 1 as necessary.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Pheasant Plucker®]

Great, thanks for the heads-up RagDyeR,

Data Validation...I knew it would have a name!

Thanks & regards,
-=pp=-

 

[Pheasant Plucker®]

Thanks for the reply Ron,

Brilliant, spot-on!

I am never amazed at what Excel can achieve but I am regularly reminded of
my own ignorance! ;^)

A couple of questions if you don't mind Ron...

1. In the Data Validation box there is an option "Apply these changes to all
other cells with the same settings"
Should I expect this to alter all other cells with this same data validation
info if for example I make one change in the Data Validation requestor for
say cell A1 by adding N/A, after Y, N, ?

Should it then change cells A2:A60 by adding the N/A, to save me
copying/pasting A2:A60?

2. How would I enter a blank space in the list? Just hit the spacebar
followed by a comma?

Thanks & regards,
-=pp=-

See if this satisfies your requirements.....

Select the input cells (I'll assume A2:A10).
Data>Validation>
Allow: List
Source: Y,N
Click the [OK] button

Select B2
B2: =IF(A2="N",1,"")
Copy that formula down as far as needed, replacing the 1 as necessary.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Sorry...don't quite know how to describe what I want to do...any telepaths
out there? ;^)

In a spreadsheet I have a need for a cell to display Y or N.

Rather than type in Y or No I would like to be able to select either option
by clicking on the cell and choosing Y or N from a small menu.

I have cut & pasted a similar cell from another spreadsheet so now when I
click in the cell there is the grey square with a black arrow displayed at
the right hand side of the cell.

On the spreadsheet I copied from there was this choice of Y or N but this
did not seem to be copied over to my new spreadsheet.

There did not appear to be a formula to the cell either.

What is the proper name for what I am trying to do and how do I get the
auto-requestor cell to display either Y or N please?

Also is there a way to define a number value in the adjacent cell if N is
selected & displayed?

For example for some entries where N is displayed I need to assign a value
of 1 in the adjacent cell and for others I need to assign a value of 2, 3 4,
or 5.

Is this even possible?

 

[Guest]

1)Yes, if you check the "Apply these changes....", then all other cells using
that same Data Validation will use the changes.

2)Why would you want to have a blank space in the list????? I hope it's not
to accommodate people who "erase" cells by putting a space in them.

***********
Regards,
Ron

XL2002, WinXP-Pro

Thanks for the reply Ron,

Brilliant, spot-on!

I am never amazed at what Excel can achieve but I am regularly reminded of
my own ignorance! ;^)

A couple of questions if you don't mind Ron...

1. In the Data Validation box there is an option "Apply these changes to all
other cells with the same settings"
Should I expect this to alter all other cells with this same data validation
info if for example I make one change in the Data Validation requestor for
say cell A1 by adding N/A, after Y, N, ?

Should it then change cells A2:A60 by adding the N/A, to save me
copying/pasting A2:A60?

2. How would I enter a blank space in the list? Just hit the spacebar
followed by a comma?

Thanks & regards,
-=pp=-

See if this satisfies your requirements.....

Select the input cells (I'll assume A2:A10).
Data>Validation>
Allow: List
Source: Y,N
Click the [OK] button

Select B2
B2: =IF(A2="N",1,"")
Copy that formula down as far as needed, replacing the 1 as necessary.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Sorry...don't quite know how to describe what I want to do...any telepaths
out there? ;^)

In a spreadsheet I have a need for a cell to display Y or N.

Rather than type in Y or No I would like to be able to select either option
by clicking on the cell and choosing Y or N from a small menu.

I have cut & pasted a similar cell from another spreadsheet so now when I
click in the cell there is the grey square with a black arrow displayed at
the right hand side of the cell.

On the spreadsheet I copied from there was this choice of Y or N but this
did not seem to be copied over to my new spreadsheet.

There did not appear to be a formula to the cell either.

What is the proper name for what I am trying to do and how do I get the
auto-requestor cell to display either Y or N please?

Also is there a way to define a number value in the adjacent cell if N is
selected & displayed?

For example for some entries where N is displayed I need to assign a value
of 1 in the adjacent cell and for others I need to assign a value of 2, 3 4,
or 5.

Is this even possible?

 

[RagDyer]

Usually, a blank space in the validation list is employed to allow the user
the freedom to enter *anything* they wish into the cell, with the actual
validation list being used strictly as a "reminder" or "auto-entry" option,
and *not* an actual restriction on the cell entry.

If this is your aim (unrestricted cell entry), there are 2 ways to go.

Select your cells, then:
<Data> <Validation> <ErrorAlert> tab,
And *Uncheck*
"Show Error Alert After InvalidEntry"
Then <OK>.

OR
You can place your 'allow list' in an out-of-the-way location on your sheet
and give it a name.
Say Z1 = Y
Z2 = N
And Z3 is blank.

Select Z1 to Z3,
Click in the 'Name Box' (left of the formula bar),
Type in "list" (no quotes),
Hit <Enter>

Now select your cells,
<Data> <Validation>
And change the contents of the 'Source' box to:
=list
Then <OK>.

Users can now click the down arrow in the validated cells to make a choice
from the existing list, OR, they can enter *anything* they wish.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks for the reply Ron,

Brilliant, spot-on!

I am never amazed at what Excel can achieve but I am regularly reminded of
my own ignorance! ;^)

A couple of questions if you don't mind Ron...

1. In the Data Validation box there is an option "Apply these changes to all
other cells with the same settings"
Should I expect this to alter all other cells with this same data validation
info if for example I make one change in the Data Validation requestor for
say cell A1 by adding N/A, after Y, N, ?

Should it then change cells A2:A60 by adding the N/A, to save me
copying/pasting A2:A60?

2. How would I enter a blank space in the list? Just hit the spacebar
followed by a comma?

Thanks & regards,
-=pp=-

See if this satisfies your requirements.....

Select the input cells (I'll assume A2:A10).
Data>Validation>
Allow: List
Source: Y,N
Click the [OK] button

Select B2
B2: =IF(A2="N",1,"")
Copy that formula down as far as needed, replacing the 1 as necessary.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Sorry...don't quite know how to describe what I want to do...any telepaths
out there? ;^)

In a spreadsheet I have a need for a cell to display Y or N.

Rather than type in Y or No I would like to be able to select either option
by clicking on the cell and choosing Y or N from a small menu.

I have cut & pasted a similar cell from another spreadsheet so now

when

I

displayed

at

2,
3 4,