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?

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?

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?

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?