Data Validation 2003
 

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

Data Validation 2003
 

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

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

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

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

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:

DataValidation
Allow: List
Source:

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

Or

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

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. Valko" wrote:

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

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

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

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:

DataValidation
Allow: List
Source:

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

Or

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

One way:

Select the first cell that has this validation
Goto EditCopy
Select the range of cells you want to copy this to
Then, EditPaste specialValidation

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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

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

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

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:

DataValidation
Allow: List
Source:

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

Or

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

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 :-)




"T. Valko" wrote:

One way:

Select the first cell that has this validation
Goto EditCopy
Select the range of cells you want to copy this to
Then, EditPaste specialValidation

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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

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

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

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:

DataValidation
Allow: List
Source:

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

Or

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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

Data Validation 2003
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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 :-)




"T. Valko" wrote:

One way:

Select the first cell that has this validation
Goto EditCopy
Select the range of cells you want to copy this to
Then, EditPaste specialValidation

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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

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

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

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:

DataValidation
Allow: List
Source:

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

Or

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


--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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. Valko" wrote:

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 a 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

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
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