Conditional Cell Validation v2.0 (Clarification)

[cypher]

On Sheet1 I have a cell B1 to which I have added VALIDATION
Data > Validation - "Settings Tab" Allow : List, Source : = PERCENTS
PERCENTS is a defined range of percentages available to the user, ONLY THESE
specified percentages can be used.

Problem is how can I combine this validation with a condition. ie, I need B1
to display a blank " " and not have the drop down accessable UNLESS
A1="YES", In which case, B1 should then have the drop-down list available,
and show a default percentage, unless the user clicks to select another
percentage.

Thank You!

-cypher






I should have clarified something. The percentage has to be user selected
from a predefined list (Validation drop-down list) from another sheet in the
workbook. I would like this drop down to be UNACCESSABLE and the cell value
to be a " " (blank space) unless A1 = YES.

Hope that more clearely demonstrates my goal. Thanks for the suggestions!!

-cypher

 

[Debra Dalgleish]

As answered at your other post:

'=====================
Create a range named "Zero" --
Type a zero in a cell
Select that cell
Click in the Name Box, at the left of the formula bar
Type: Zero
then press the Enter key

Create a range with your percents, named "RateList" --
Type your rates in a list in the workbook
Select those cells
Click in the Name Box, at the left of the formula bar
Type: RateList
then press the Enter key

Select cell B1
From the Allow dropdown, choose: List
In the Source box, type:
=IF(A1="Yes",RateList,Zero)
Click OK
'========================

On Sheet1 I have a cell B1 to which I have added VALIDATION
Data > Validation - "Settings Tab" Allow : List, Source : = PERCENTS
PERCENTS is a defined range of percentages available to the user, ONLY THESE
specified percentages can be used.

Problem is how can I combine this validation with a condition. ie, I need B1
to display a blank " " and not have the drop down accessable UNLESS
A1="YES", In which case, B1 should then have the drop-down list available,
and show a default percentage, unless the user clicks to select another
percentage.

Thank You!

-cypher






I should have clarified something. The percentage has to be user selected
from a predefined list (Validation drop-down list) from another sheet in the
workbook. I would like this drop down to be UNACCESSABLE and the cell value
to be a " " (blank space) unless A1 = YES.

Hope that more clearely demonstrates my goal. Thanks for the suggestions!!

-cypher

 

[cypher]

Debra,
Thank you so much. I think this is EXACTLY what I am looking for. Will
advise. Thanks again!

-cypher

As answered at your other post:

'=====================
Create a range named "Zero" --
Type a zero in a cell
Select that cell
Click in the Name Box, at the left of the formula bar
Type: Zero
then press the Enter key

Create a range with your percents, named "RateList" --
Type your rates in a list in the workbook
Select those cells
Click in the Name Box, at the left of the formula bar
Type: RateList
then press the Enter key

Select cell B1
From the Allow dropdown, choose: List
In the Source box, type:
=IF(A1="Yes",RateList,Zero)
Click OK
'========================

 

[cypher]

This is very very close to what I was looking to do. Thanks. Question now
is...once A1 changes to either YES or NO (which then as you've assisted me
to do, B1 has different drop down lists) Can I force a default value? ie, If
I set A1 to YES, B1 now has various percentages available. I select 5% lets
say. Now I go BACK and switch A1 to NO, here, B1 remains at 5%. Sure if I
hit the drop down, only the "Zero" range is available, but until I select
the Zero value, the Percent in B1 is still affecting othe calculations.

What I'd like to see, is switching A1 to YES or NO, immediately effect a
DEFAULT start value in B1. If a percent is currently in B1, an A1 switch to
NO should immediately effect a blank in B1. Vice-versa, if B1 is Blank, an
A1 switch to YES, should immediately effect a switch to a default percentage
value (would it NEED to be the first value in the "RateList" ??)

Sure appreciate the great help!

-cypher

As answered at your other post:

'=====================
Create a range named "Zero" --
Type a zero in a cell
Select that cell
Click in the Name Box, at the left of the formula bar
Type: Zero
then press the Enter key

Create a range with your percents, named "RateList" --
Type your rates in a list in the workbook
Select those cells
Click in the Name Box, at the left of the formula bar
Type: RateList
then press the Enter key

Select cell B1
From the Allow dropdown, choose: List
In the Source box, type:
=IF(A1="Yes",RateList,Zero)
Click OK
'========================

 

[Debra Dalgleish]

Type Yes and No in a list on the worksheet
Select these two cells, and name the range YesNo

Select the cell that contains the Yes, and name it Yes

Select cell A1
From the Allow dropdown, choose: List
In the Source box, type:
=IF(B1=0,YesNo,Yes)
Click OK

 

[cypher]

Debra, thanks again for your quick assistance. This now does in fact
PREVENT the user from having A1 = No while there being a percent being
present in B1.

Please bare with me if I can ask, what I'd like to do is still have the
functional A1 (Yes/No) option, but once selecting NO, have B1 go to zero or
MOST preferrably for design purposes, just have a blank space or be entirely
empty. Once A1 is set as YES, have a default percentage pop up.

So to sum up, no matter what A1 is set to, Yes or No, have B1 have a default
value (with acompanying verification list) for both values of A1. A1=Yes
B1=Blank, A1=NO, B1 = start value 5.00%

Please forgive me if I am not explaning myself well.

-cypher

 

[cypher]

Debra, looked at your excellent site.

What I'd like to do, I think you have closely approximated in:
ProductsList.xls
http://www.contextures.com/excelfiles.html
Product List by Category -- uses Data Validation and an Advanced Filter to
extract a list of products for the selected category; file contains a macro
which automates the filter (XL2000 and XL2002 only) ProductsList.xls 35 kb;

Let's just use cell B7 from ProductsList.xls as an example.

As it now stands, B7 = "Queso Cabrales" when C3="Dairy Products"
ALSO B13="Camembert Pierrot" when C3="Dairy Products"

Once C3 is changed to "Meat/Poultry" B7 now = "Mishi Kobe Niku"
AND B13 is Blank!!!


What I'd like to know from your example, is if, B7 or B13 could be DropDown
lists just like C3, whose list contents would vary depending on the value of
C3

BUT AT THE SAME TIME!! Have a default value! As in C3 = "Dairy Products"
Now B7 has a drop down containing:

Queso Cabrales
Queso Manchego La Pastora
Gorgonzola Telino
Mascarpone Fabioli
Geitost
Raclette Courdavault
Camembert Pierrot
Gudbrandsdalsost
Fløtemysost
Mozzarella di Giovanni


From which I choose...Gorgonzola Telino

NOW, I then go BACK to C3 and change it to Meat/Poultry. I am trying to now
ENSURE that B7 DOES NOT still say "Gorgonzola Telino", but rather some
default item from it's now corresponding drop down list:

Mishi Kobe Niku
Alice Mutton
Thüringer Rostbratwurst
Perth Pasties
Tourtière
Pâté chinois


Perhaps once C3 is changed to Meats/Poultry, B7 should IMMEDIATELY change
from "Gorgonzola Telino" to "Mishi Kobe Niku"

So what I am looking for is one cell (B1) to have 2 different drop down
lists whose availability depends entirely on the value of another cell (A1).
The value of B1 should immediately change following the "Change Event" that
is initiated by changing the value of A1 from one value to another.

FURTHER CLARIFICATION EXAMPLE:
Two lists have been created, one called FRUITS, and the other VEGETABLES

A1 set to Verify that it can equal either FRUIT or VEGETABLES
B1 set to verify a drop down list of either FRUITS or VEGETABLES

Set A1 to FRUITS, B1 should display some entry from the FRUITS list, say
BANANA, we then change it to some other available value, say APPLE
Go back to A1 and change it to VEGETABLES
B1 should no longer say APPLE, nor should it be BLANK, but it should display
some value from the VEGETABLES list.
======================================================
Thank you for baring with me, I certainly am trying to investigate how to
properly implement this desired feature. Perhaps some VBA code is
necessary?

Thanks!

-cypher

 

[Debra Dalgleish]

You could use the Worksheet_Change event for that.

Create a list named Yes, with the options for the Yes dropdown list
Create a list named No, with the options for the No dropdown list.

Add the following code to the worksheet module:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").Value = Application.WorksheetFunction _
.Index(Sheets("Lists").Range(Target.Value), 1)
End If
End Sub
'=======================

I added a sample file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Default to First Value'

 

[cypher]

Exactly!!!!

Thank you so much!!!

-cypher

You could use the Worksheet_Change event for that.

Create a list named Yes, with the options for the Yes dropdown list
Create a list named No, with the options for the No dropdown list.

Add the following code to the worksheet module:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").Value = Application.WorksheetFunction _
.Index(Sheets("Lists").Range(Target.Value), 1)
End If
End Sub
'=======================

I added a sample file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'Default to First Value'

 

[Debra Dalgleish]

You're welcome!

Exactly!!!!

Thank you so much!!!

-cypher



value of