Data Validation 2003

L

Lisa

Hi
I have created a list in column E which contains 3 values. If the use
selects a value from this list, but the cell on column C is blank (also a
list), I want to issue a warning to the user to tell them to put a value
column C (which is also a list) before they can pick from the list in column
E. I presume I must use data validation for this, and have been
experimenting, but I'm not sure how to combine the original list and an 'if'
formula within the same rule

Would appreciate any help
Thanks
 
T

T. Valko

I have created a list in column E which contains 3 values

I assume that means it's a data validation list?

Let's assume the selections for this list are: Yes, No, Maybe

Put these values in a range of cells, say, J1:J3

Give the range J1:J3 a defined name, say, List.

Let's assume the data validation list is in cell E1 and you don't want it to
work unless an entry is made in cell C1.

As the source for the drop down list in cell E1 use this formula:

=IF(C1="",NA(),List)

When you're setting this up and you get a message that says something like:
the source currently evaluates to an error......

Just answer: Yes
 
L

Lisa

Hi there.

I tried your suggestion, but it didnt work. I was simply allowed to carry on

Also, my examples in the explanation were a bit theoretical, rather than the
real ones, so apologies for this. hopefully the specific description below
gives you a bit more info

My List exists on a sheet called "lists" and is called "fback" - it
contains two values "Checked" and "Awaiting Feedback"

I then have another validation rule on a sheet called "Contacts" in Column
X, which is type of List and has the value of "=fback", So when I select the
drop down it shows me the list as above.

I then have another drop down list on Contacts sheet in column S containing
the values "Master", "Duplicate". What I wanting the new validation rule to
do is prevent the user selecting "Checked" or "Awaiting Feedback" without
selecting "Duplicate" or "Master" in Column C

I think the formula you specified below would work, but I wondered which
validation to put it against, and whether that validation should be a type of
List or Custom?

Many Thanks
 
T

T. Valko

When you're setting this up and you get a message that says
I tried your suggestion, but it didnt work. I was simply allowed to carry
on
Click to expand...

You'll only get that message if the source cell is empty while you're
setting up the validation.

I'm a bit confused....
I then have another drop down list on Contacts sheet in column S
containing
the values "Master", "Duplicate". What I wanting the new validation rule
to
do is prevent the user selecting "Checked" or "Awaiting Feedback" without
selecting "Duplicate" or "Master" in Column C
Click to expand...

You say the drop down is in column S but then you say column C?

In either case, what I suggested does work. As the source for the drop down
in column X you would use this formula:

Data>Validation
Allow: List
Source:

=IF(C1="",NA(),fback)

Or

=IF(S1="",NA(),fback)
 
L

Lisa

Hi

Many thanks for your response. I'll give it another go and let you know how
I get on. One more thing though, how can I apply the validation rule to the
whole column, or would this be done by a macro?

Thanks
 
T

T. Valko

One way:

Select the first cell that has this validation
Goto Edit>Copy
Select the range of cells you want to copy this to
Then, Edit>Paste special>Validation

The source formula will automatically increment like this:

=IF(C1="",NA(),fback)
=IF(C2="",NA(),fback)
=IF(C3="",NA(),fback)
=IF(C4="",NA(),fback)
etc
etc
 
L

Lisa

Absolutely perfect. Thanks so much

Thanks very much for you help. I understand my confusion from earlier now,
in that I was expecting my error to pop up, but the formula actually prevents
the drop down list from appearing, which is fine.

Many thanks :)
 
Top