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 amp; 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 amp; 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?
--
Thanks amp; regards,
-pp-
You can easily create these drop-down boxes using quot;Data Validationquot;.
Check out the many pages Debra Dalgleish has on her web site concerning this
subject, starting with:
www.contextures.com/xlDataVal01.html
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===quot;Pheasant Plucker®quot; gt; wrote in message
...
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 amp; 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 amp; 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?
--
Thanks amp; regards,
-pp-
See if this satisfies your requirements.....
Select the input cells (I'll assume A2:A10).
Datagt;Validationgt;
Allow: List
Source: Y,N
Click the [OK] button
Select B2
B2: =IF(A2=quot;Nquot;,1,quot;quot;)
Copy that formula down as far as needed, replacing the 1 as necessary.
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Proquot;Pheasant Plucker®quot; wrote:
gt; Sorry...don't quite know how to describe what I want to do...any telepaths
gt; out there? ;^)
gt;
gt; In a spreadsheet I have a need for a cell to display Y or N.
gt;
gt; Rather than type in Y or No I would like to be able to select either option
gt; by clicking on the cell and choosing Y or N from a small menu.
gt;
gt; I have cut amp; pasted a similar cell from another spreadsheet so now when I
gt; click in the cell there is the grey square with a black arrow displayed at
gt; the right hand side of the cell.
gt;
gt; On the spreadsheet I copied from there was this choice of Y or N but this
gt; did not seem to be copied over to my new spreadsheet.
gt;
gt; There did not appear to be a formula to the cell either.
gt;
gt; What is the proper name for what I am trying to do and how do I get the
gt; auto-requestor cell to display either Y or N please?
gt;
gt; Also is there a way to define a number value in the adjacent cell if N is
gt; selected amp; displayed?
gt;
gt; For example for some entries where N is displayed I need to assign a value
gt; of 1 in the adjacent cell and for others I need to assign a value of 2, 3 4,
gt; or 5.
gt;
gt; Is this even possible?
gt;
gt; --
gt; Thanks amp; regards,
gt; -pp-
gt;
gt;
gt;
Great, thanks for the heads-up RagDyeR,
Data Validation...I knew it would have a name!
Thanks amp; regards,
-=pp=-quot;RagDyeRquot; gt; wrote in message
...
gt; You can easily create these drop-down boxes using quot;Data Validationquot;.
gt;
gt; Check out the many pages Debra Dalgleish has on her web site concerning
this
gt; subject, starting with:
gt;
gt; www.contextures.com/xlDataVal01.html
gt;
gt; --
gt;
gt; HTH,
gt;
gt; RD
gt; ================================================== ===
gt; Please keep all correspondence within the Group, so all may benefit!
gt; ================================================== ===
gt;
gt;
gt; quot;Pheasant Plucker®quot; gt; wrote in message
gt; ...
gt; Sorry...don't quite know how to describe what I want to do...any telepaths
gt; out there? ;^)
gt;
gt; In a spreadsheet I have a need for a cell to display Y or N.
gt;
gt; Rather than type in Y or No I would like to be able to select either
option
gt; by clicking on the cell and choosing Y or N from a small menu.
gt;
gt; I have cut amp; pasted a similar cell from another spreadsheet so now when I
gt; click in the cell there is the grey square with a black arrow displayed at
gt; the right hand side of the cell.
gt;
gt; On the spreadsheet I copied from there was this choice of Y or N but this
gt; did not seem to be copied over to my new spreadsheet.
gt;
gt; There did not appear to be a formula to the cell either.
gt;
gt; What is the proper name for what I am trying to do and how do I get the
gt; auto-requestor cell to display either Y or N please?
gt;
gt; Also is there a way to define a number value in the adjacent cell if N is
gt; selected amp; displayed?
gt;
gt; For example for some entries where N is displayed I need to assign a value
gt; of 1 in the adjacent cell and for others I need to assign a value of 2, 3
4,
gt; or 5.
gt;
gt; Is this even possible?
gt;
gt; --
gt; Thanks amp; regards,
gt; -pp-
gt;
gt;
gt;
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 quot;Apply these changes to all
other cells with the same settingsquot;
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 amp; regards,
-=pp=-
quot;Ron Coderrequot; gt; wrote in message
...
gt; See if this satisfies your requirements.....
gt;
gt; Select the input cells (I'll assume A2:A10).
gt; Datagt;Validationgt;
gt; Allow: List
gt; Source: Y,N
gt; Click the [OK] button
gt;
gt; Select B2
gt; B2: =IF(A2=quot;Nquot;,1,quot;quot;)
gt; Copy that formula down as far as needed, replacing the 1 as necessary.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Pheasant Plucker®quot; wrote:
gt;
gt; gt; Sorry...don't quite know how to describe what I want to do...any
telepaths
gt; gt; out there? ;^)
gt; gt;
gt; gt; In a spreadsheet I have a need for a cell to display Y or N.
gt; gt;
gt; gt; Rather than type in Y or No I would like to be able to select either
option
gt; gt; by clicking on the cell and choosing Y or N from a small menu.
gt; gt;
gt; gt; I have cut amp; pasted a similar cell from another spreadsheet so now when
I
gt; gt; click in the cell there is the grey square with a black arrow displayed
at
gt; gt; the right hand side of the cell.
gt; gt;
gt; gt; On the spreadsheet I copied from there was this choice of Y or N but
this
gt; gt; did not seem to be copied over to my new spreadsheet.
gt; gt;
gt; gt; There did not appear to be a formula to the cell either.
gt; gt;
gt; gt; What is the proper name for what I am trying to do and how do I get the
gt; gt; auto-requestor cell to display either Y or N please?
gt; gt;
gt; gt; Also is there a way to define a number value in the adjacent cell if N
is
gt; gt; selected amp; displayed?
gt; gt;
gt; gt; For example for some entries where N is displayed I need to assign a
value
gt; gt; of 1 in the adjacent cell and for others I need to assign a value of 2,
3 4,
gt; gt; or 5.
gt; gt;
gt; gt; Is this even possible?
gt; gt;
gt; gt; --
gt; gt; Thanks amp; regards,
gt; gt; -pp-
gt; gt;
gt; gt;
gt; gt;
1)Yes, if you check the quot;Apply these changes....quot;, 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 quot;erasequot; cells by putting a space in them.
***********
Regards,
Ron
XL2002, WinXP-Proquot;Pheasant Plucker®quot; wrote:
gt; Thanks for the reply Ron,
gt;
gt; Brilliant, spot-on!
gt;
gt; I am never amazed at what Excel can achieve but I am regularly reminded of
gt; my own ignorance! ;^)
gt;
gt; A couple of questions if you don't mind Ron...
gt;
gt; 1. In the Data Validation box there is an option quot;Apply these changes to all
gt; other cells with the same settingsquot;
gt; Should I expect this to alter all other cells with this same data validation
gt; info if for example I make one change in the Data Validation requestor for
gt; say cell A1 by adding N/A, after Y, N, ?
gt;
gt; Should it then change cells A2:A60 by adding the N/A, to save me
gt; copying/pasting A2:A60?
gt;
gt; 2. How would I enter a blank space in the list? Just hit the spacebar
gt; followed by a comma?
gt;
gt; Thanks amp; regards,
gt; -=pp=-
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt; See if this satisfies your requirements.....
gt; gt;
gt; gt; Select the input cells (I'll assume A2:A10).
gt; gt; Datagt;Validationgt;
gt; gt; Allow: List
gt; gt; Source: Y,N
gt; gt; Click the [OK] button
gt; gt;
gt; gt; Select B2
gt; gt; B2: =IF(A2=quot;Nquot;,1,quot;quot;)
gt; gt; Copy that formula down as far as needed, replacing the 1 as necessary.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Pheasant Plucker®quot; wrote:
gt; gt;
gt; gt; gt; Sorry...don't quite know how to describe what I want to do...any
gt; telepaths
gt; gt; gt; out there? ;^)
gt; gt; gt;
gt; gt; gt; In a spreadsheet I have a need for a cell to display Y or N.
gt; gt; gt;
gt; gt; gt; Rather than type in Y or No I would like to be able to select either
gt; option
gt; gt; gt; by clicking on the cell and choosing Y or N from a small menu.
gt; gt; gt;
gt; gt; gt; I have cut amp; pasted a similar cell from another spreadsheet so now when
gt; I
gt; gt; gt; click in the cell there is the grey square with a black arrow displayed
gt; at
gt; gt; gt; the right hand side of the cell.
gt; gt; gt;
gt; gt; gt; On the spreadsheet I copied from there was this choice of Y or N but
gt; this
gt; gt; gt; did not seem to be copied over to my new spreadsheet.
gt; gt; gt;
gt; gt; gt; There did not appear to be a formula to the cell either.
gt; gt; gt;
gt; gt; gt; What is the proper name for what I am trying to do and how do I get the
gt; gt; gt; auto-requestor cell to display either Y or N please?
gt; gt; gt;
gt; gt; gt; Also is there a way to define a number value in the adjacent cell if N
gt; is
gt; gt; gt; selected amp; displayed?
gt; gt; gt;
gt; gt; gt; For example for some entries where N is displayed I need to assign a
gt; value
gt; gt; gt; of 1 in the adjacent cell and for others I need to assign a value of 2,
gt; 3 4,
gt; gt; gt; or 5.
gt; gt; gt;
gt; gt; gt; Is this even possible?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Thanks amp; regards,
gt; gt; gt; -pp-
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;
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 quot;reminderquot; or quot;auto-entryquot; 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:
lt;Datagt; lt;Validationgt; lt;ErrorAlertgt; tab,
And *Uncheck*
quot;Show Error Alert After InvalidEntryquot;
Then lt;OKgt;.
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 quot;listquot; (no quotes),
Hit lt;Entergt;
Now select your cells,
lt;Datagt; lt;Validationgt;
And change the contents of the 'Source' box to:
=list
Then lt;OKgt;.
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!
==============================================quot;Pheasant Plucker®quot; gt; wrote in message
...
gt; Thanks for the reply Ron,
gt;
gt; Brilliant, spot-on!
gt;
gt; I am never amazed at what Excel can achieve but I am regularly reminded of
gt; my own ignorance! ;^)
gt;
gt; A couple of questions if you don't mind Ron...
gt;
gt; 1. In the Data Validation box there is an option quot;Apply these changes to
all
gt; other cells with the same settingsquot;
gt; Should I expect this to alter all other cells with this same data
validation
gt; info if for example I make one change in the Data Validation requestor for
gt; say cell A1 by adding N/A, after Y, N, ?
gt;
gt; Should it then change cells A2:A60 by adding the N/A, to save me
gt; copying/pasting A2:A60?
gt;
gt; 2. How would I enter a blank space in the list? Just hit the spacebar
gt; followed by a comma?
gt;
gt; Thanks amp; regards,
gt; -=pp=-
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt; See if this satisfies your requirements.....
gt; gt;
gt; gt; Select the input cells (I'll assume A2:A10).
gt; gt; Datagt;Validationgt;
gt; gt; Allow: List
gt; gt; Source: Y,N
gt; gt; Click the [OK] button
gt; gt;
gt; gt; Select B2
gt; gt; B2: =IF(A2=quot;Nquot;,1,quot;quot;)
gt; gt; Copy that formula down as far as needed, replacing the 1 as necessary.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Pheasant Plucker®quot; wrote:
gt; gt;
gt; gt; gt; Sorry...don't quite know how to describe what I want to do...any
gt; telepaths
gt; gt; gt; out there? ;^)
gt; gt; gt;
gt; gt; gt; In a spreadsheet I have a need for a cell to display Y or N.
gt; gt; gt;
gt; gt; gt; Rather than type in Y or No I would like to be able to select either
gt; option
gt; gt; gt; by clicking on the cell and choosing Y or N from a small menu.
gt; gt; gt;
gt; gt; gt; I have cut amp; pasted a similar cell from another spreadsheet so now
when
gt; I
gt; gt; gt; click in the cell there is the grey square with a black arrow
displayed
gt; at
gt; gt; gt; the right hand side of the cell.
gt; gt; gt;
gt; gt; gt; On the spreadsheet I copied from there was this choice of Y or N but
gt; this
gt; gt; gt; did not seem to be copied over to my new spreadsheet.
gt; gt; gt;
gt; gt; gt; There did not appear to be a formula to the cell either.
gt; gt; gt;
gt; gt; gt; What is the proper name for what I am trying to do and how do I get
the
gt; gt; gt; auto-requestor cell to display either Y or N please?
gt; gt; gt;
gt; gt; gt; Also is there a way to define a number value in the adjacent cell if N
gt; is
gt; gt; gt; selected amp; displayed?
gt; gt; gt;
gt; gt; gt; For example for some entries where N is displayed I need to assign a
gt; value
gt; gt; gt; of 1 in the adjacent cell and for others I need to assign a value of
2,
gt; 3 4,
gt; gt; gt; or 5.
gt; gt; gt;
gt; gt; gt; Is this even possible?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Thanks amp; regards,
gt; gt; gt; -pp-
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
留言列表
