Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.

You could use Data Validation for this.

Select cells C2 through G2 (or whichever cells you want to restrict).
From the Data Menu, select "Validation"
Change the Allow Field to "Custom"
Enter the formula =COUNTIF($B2,"A")=1
Set Input and Error messages if you wish
Click OK

That should do it.

HTH,
Elkar


"ron" wrote:

Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.

Great.
Is there a way to make those restricted cells be filled with hatch pattern
when the restriction occurs.

"Elkar" wrote:

You could use Data Validation for this.

Select cells C2 through G2 (or whichever cells you want to restrict).
From the Data Menu, select "Validation"
Change the Allow Field to "Custom"
Enter the formula =COUNTIF($B2,"A")=1
Set Input and Error messages if you wish
Click OK

That should do it.

HTH,
Elkar


"ron" wrote:

Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.

Sure, you can use Conditional Formatting for that.

Select the same cells as you did for Validation
From the Format Menu, select "Conditional Formatting..."
Change "Cell Value Is" to "Formula Is"
Enter the formula: =COUNTIF($B2,"A")<1
Set your format
Click OK

HTH,
Elkar


"ron" wrote:

Great.
Is there a way to make those restricted cells be filled with hatch pattern
when the restriction occurs.

"Elkar" wrote:

You could use Data Validation for this.

Select cells C2 through G2 (or whichever cells you want to restrict).
From the Data Menu, select "Validation"
Change the Allow Field to "Custom"
Enter the formula =COUNTIF($B2,"A")=1
Set Input and Error messages if you wish
Click OK

That should do it.

HTH,
Elkar


"ron" wrote:

Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.

Great, again, but I think I hit a snag.

Some of the cells I want restricted already have DV for dropdown lists.
Is that a death nail to our efforts?


"Elkar" wrote:

Sure, you can use Conditional Formatting for that.

Select the same cells as you did for Validation
From the Format Menu, select "Conditional Formatting..."
Change "Cell Value Is" to "Formula Is"
Enter the formula: =COUNTIF($B2,"A")<1
Set your format
Click OK

HTH,
Elkar


"ron" wrote:

Great.
Is there a way to make those restricted cells be filled with hatch pattern
when the restriction occurs.

"Elkar" wrote:

You could use Data Validation for this.

Select cells C2 through G2 (or whichever cells you want to restrict).
From the Data Menu, select "Validation"
Change the Allow Field to "Custom"
Enter the formula =COUNTIF($B2,"A")=1
Set Input and Error messages if you wish
Click OK

That should do it.

HTH,
Elkar


"ron" wrote:

Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.

That does complicate things a bit. There are still a couple options though.

First off, if you're willing to lose the drop-down lists, you could use the
AND and OR functions to maintain entry of only those list items. The
downside would be that the user would no longer have the function of a
drop-down list. They would have to type in the data. The DV formula for
this would look something like:

=AND(COUNTIF($B2,"A")=1,OR(C2="Item 1",C2="Item 2",C2="Item 3"))

The other option would be to use VB code. You might want to ask for help in
the Excel Programming Discussion board if you want to pursue this option.
One downside to this option would be that it would only work if the user
chooses to "Enable Macros" when they open the workbook.

HTH,
Elkar


"ron" wrote:

Great, again, but I think I hit a snag.

Some of the cells I want restricted already have DV for dropdown lists.
Is that a death nail to our efforts?


"Elkar" wrote:

Sure, you can use Conditional Formatting for that.

Select the same cells as you did for Validation
From the Format Menu, select "Conditional Formatting..."
Change "Cell Value Is" to "Formula Is"
Enter the formula: =COUNTIF($B2,"A")<1
Set your format
Click OK

HTH,
Elkar


"ron" wrote:

Great.
Is there a way to make those restricted cells be filled with hatch pattern
when the restriction occurs.

"Elkar" wrote:

You could use Data Validation for this.

Select cells C2 through G2 (or whichever cells you want to restrict).
From the Data Menu, select "Validation"
Change the Allow Field to "Custom"
Enter the formula =COUNTIF($B2,"A")=1
Set Input and Error messages if you wish
Click OK

That should do it.

HTH,
Elkar


"ron" wrote:

Say the list is 7 columns wide. If I put "A" in B2, I want to be able to
input data in C2 thru G2. But, if I put "B" or "P" in B2, I want cells C2, E2
and F2 to allow no data to be entered. If the B2 entry is changed to "A", all
cells C2 thru G2 become available for entry again.