Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
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? -- Thanks & regards, -pp- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
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! ================================================== === "Pheasant Plucker®" 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 & 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? -- Thanks & regards, -pp- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
See if this satisfies your requirements.....
Select the input cells (I'll assume A2:A10). DataValidation 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Ā®" wrote: 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? -- Thanks & regards, -pp- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
Great, thanks for the heads-up RagDyeR,
Data Validation...I knew it would have a name! Thanks & regards, -=pp=- "RagDyeR" wrote in message ... 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! ================================================== === "Pheasant Plucker®" 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 & 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? -- Thanks & regards, -pp- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
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=- "Ron Coderre" wrote in message ... See if this satisfies your requirements..... Select the input cells (I'll assume A2:A10). DataValidation 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®" wrote: 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? -- Thanks & regards, -pp- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
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 "Pheasant PluckerĀ®" wrote: 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=- "Ron Coderre" wrote in message ... See if this satisfies your requirements..... Select the input cells (I'll assume A2:A10). DataValidation 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Ā®" wrote: 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? -- Thanks & regards, -pp- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to setup a 'chooser' box/cell for Y or No?
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! ============================================== "Pheasant Plucker®" wrote in message ... 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=- "Ron Coderre" wrote in message ... See if this satisfies your requirements..... Select the input cells (I'll assume A2:A10). DataValidation 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®" wrote: 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? -- Thanks & regards, -pp- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel; how do I print titles in 'Setup' when option greyed out? | Excel Worksheet Functions | |||
Help with Multiple Worksheet Setup | Excel Discussion (Misc queries) | |||
Control cursor direction in highlighted group of cells/Page setup | Excel Worksheet Functions | |||
How do I copy footer setup from one tap to many tabs? | Excel Discussion (Misc queries) | |||
can you set the "Page Setup" margins etc. differently for each pag | Excel Worksheet Functions |