If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data restrictions
I have created a spreadsheet with drop down boxes that rely on one selection
to determine the choices for the next selection. For example, if you select "Base" or "Upgrade" in the first drop down box(column heading is Option), the second drop down box will list available color selections for the selected option. I have protected the worksheet so only these drop down boxes are accessable. unfortunaly a user can still do the following: 1. Type into the cell any value they choose ignoring the drop down box 2. Select an option and a color selection associated with that option and then change the option without being required to change the color selection. How do I limit the cell input to only one of the selections in the drop down box and how do I restrict the color selections to those availabe to the selected option? |
#2
|
|||
|
|||
data restrictions
hi,
try to use data validation, see the Debra tips at: http://www.contextures.com/xlDataVal10.html http://www.contextures.com/xlDataVal02.html hth regards from Brazil Marcelo "contractormike" escreveu: I have created a spreadsheet with drop down boxes that rely on one selection to determine the choices for the next selection. For example, if you select "Base" or "Upgrade" in the first drop down box(column heading is Option), the second drop down box will list available color selections for the selected option. I have protected the worksheet so only these drop down boxes are accessable. unfortunaly a user can still do the following: 1. Type into the cell any value they choose ignoring the drop down box 2. Select an option and a color selection associated with that option and then change the option without being required to change the color selection. How do I limit the cell input to only one of the selections in the drop down box and how do I restrict the color selections to those availabe to the selected option? |
#3
|
|||
|
|||
data restrictions
Hi contractormike,
I agree with Marcelo. I have been trying this for a while and have eventually got it to work. In addition to a little help from this forum, I also found Debra's sites useful. http://www.contextures.com/xlDataVal10.html http://www.contextures.com/xlDataVal02.html Well worth a look and a print out! In summary, this is what you'll be doing. Using Data Validation, you will need to set up your main validation list first - this will be based on the choices of Base and Upgrade. You can then set up your data validation allowing only items on this list. Next, you will create a range called Base, with the colours in it you need, and another range called Upgrade, again listing the colours you require. Ensure you spell the range names of the sublists to match the choices from your main list. Then using the Indirect function, you will create your next set of validation which is dependent on what one chooses from the first list. So, if someone chooses Base from the first list, the dropdown they see should be based on the range called Base. If someone chooses Upgrade, then their set of choices should be based on those listed in the range called Upgrade. Following Debra's example you should not get lost. Let me know how you get on, and if you get stuck with this I'll see what I can do. Take care, Karen. "contractormike" wrote: I have created a spreadsheet with drop down boxes that rely on one selection to determine the choices for the next selection. For example, if you select "Base" or "Upgrade" in the first drop down box(column heading is Option), the second drop down box will list available color selections for the selected option. I have protected the worksheet so only these drop down boxes are accessable. unfortunaly a user can still do the following: 1. Type into the cell any value they choose ignoring the drop down box 2. Select an option and a color selection associated with that option and then change the option without being required to change the color selection. How do I limit the cell input to only one of the selections in the drop down box and how do I restrict the color selections to those availabe to the selected option? |
#4
|
|||
|
|||
data restrictions
I have already created the dependent drop down boxes. The problem I am
finding is if a color is selected from the "Base" option and the user decides to change the option to "Upgrade", the first color selection could be left unchanged in the cell. Also, users are not restricted to making a selection from the drop down box. I don't want users to be able to type an entry in a cell where I have a drop down box. How do I restrict these cells to only accept the info in the drop down box and to verify the color selected goes with the appropriate option? "KarenF" wrote: Hi contractormike, I agree with Marcelo. I have been trying this for a while and have eventually got it to work. In addition to a little help from this forum, I also found Debra's sites useful. http://www.contextures.com/xlDataVal10.html http://www.contextures.com/xlDataVal02.html Well worth a look and a print out! In summary, this is what you'll be doing. Using Data Validation, you will need to set up your main validation list first - this will be based on the choices of Base and Upgrade. You can then set up your data validation allowing only items on this list. Next, you will create a range called Base, with the colours in it you need, and another range called Upgrade, again listing the colours you require. Ensure you spell the range names of the sublists to match the choices from your main list. Then using the Indirect function, you will create your next set of validation which is dependent on what one chooses from the first list. So, if someone chooses Base from the first list, the dropdown they see should be based on the range called Base. If someone chooses Upgrade, then their set of choices should be based on those listed in the range called Upgrade. Following Debra's example you should not get lost. Let me know how you get on, and if you get stuck with this I'll see what I can do. Take care, Karen. "contractormike" wrote: I have created a spreadsheet with drop down boxes that rely on one selection to determine the choices for the next selection. For example, if you select "Base" or "Upgrade" in the first drop down box(column heading is Option), the second drop down box will list available color selections for the selected option. I have protected the worksheet so only these drop down boxes are accessable. unfortunaly a user can still do the following: 1. Type into the cell any value they choose ignoring the drop down box 2. Select an option and a color selection associated with that option and then change the option without being required to change the color selection. How do I limit the cell input to only one of the selections in the drop down box and how do I restrict the color selections to those availabe to the selected option? |
Thread Tools | |
Display Modes | |
|
|