Help! Combining Validation and Formula

[Big Blue]

I would like to do the following:
if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

I bow down to your expertise...........

 

[Aladin Akyurek]

On 2002-04-03 11:53, Big Blue wrote:
I would like to do the following:
if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

I bow down to your expertise...........

Probably not quite what you want, but you could X in some cell say in a sheet called Admin. Select this cell and name it List1. Put underneath this X-cell, Y and Z. Select the cells with Y and Z and name the selection List2.

In data validation that you set up in B5 opt for List for Allow and enter the following formula:

=IF(A1=0,List1,List2)

List1 will be active if A1 is empty or houses the number 0. If a real 0 must be the value of A1 for X, then use:

=IF(AND(LEN(A1)=0,A1=0),List1,List2)

Aladin

 

[Big Blue]

So close! Thankyou!
My problem is that if the user doesn't remember to fill in cell a1,(ie a1 =0) I want cell b5 to default to X automatically. If the user does fill in cell a1, then I want to offer dropdown list1 in B5.

Your solution is great, but it requires user to enter default in B5 by selecting from "other" list.