using the if statement with data validation

I have two cells, we'll call them A1, and B1. Both cells are set up to be
drop down lists that all users to pick a pre-determined value of the cell.
I did this by putting the pre determined value in cells hidden from the user
and then highlighting the cell, choosing "Data" then choosing "Validation"
and then pointing to the hidden data cell range.

One of the values/choises in the drop down of cell A1 is "None". If the
user chooses "None" then there is no need for the user to pick from the drop
down list in cell B2. Cell B2 should automatically populate with "None" as
well.

Since cell B1 is set up as a data valadition, it won't allow me to put an
=if function for the "None" value of cell A1.

Any ideas?

Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GreenMonster" <[email protected]> wrote in message
news:[email protected]...
> I have two cells, we'll call them A1, and B1. Both cells are set up to be
> drop down lists that all users to pick a pre-determined value of the cell.
> I did this by putting the pre determined value in cells hidden from the
user
> and then highlighting the cell, choosing "Data" then choosing "Validation"
> and then pointing to the hidden data cell range.
>
> One of the values/choises in the drop down of cell A1 is "None". If the
> user chooses "None" then there is no need for the user to pick from the
drop
> down list in cell B2. Cell B2 should automatically populate with "None"
as
> well.
>
> Since cell B1 is set up as a data valadition, it won't allow me to put an
> =if function for the "None" value of cell A1.
>
> Any ideas?
>
>

Near your other lists, create a list with the single item "None"
Name that list: None
There are instructions here for naming a range:
http://www.contextures.com/xlNames01.html

Then in the data validation for cell B1, choose Allow: List
In the Source box, type:
=IF(A1="None",None,DayList)
Substitute your list name for "DayList"

To prevent some problems, you may also want to change the validation in
cell A1.
In the data validation for cell A1, choose Allow: List
In the Source box, type:
=IF(B1="None",None,MonthList)
Substitute your list name for "MonthList"

This will force users to clear a "None" from B1 before they can choose a
different item in A1.

To automatically enter a "none" in B1, you could use programming


GreenMonster wrote:
> I have two cells, we'll call them A1, and B1. Both cells are set up to be
> drop down lists that all users to pick a pre-determined value of the cell.
> I did this by putting the pre determined value in cells hidden from the user
> and then highlighting the cell, choosing "Data" then choosing "Validation"
> and then pointing to the hidden data cell range.
>
> One of the values/choises in the drop down of cell A1 is "None". If the
> user chooses "None" then there is no need for the user to pick from the drop
> down list in cell B2. Cell B2 should automatically populate with "None" as
> well.
>
> Since cell B1 is set up as a data valadition, it won't allow me to put an
> =if function for the "None" value of cell A1.
>
> Any ideas?
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html