#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 76
Default Validation Formula

Hi everyone. I need a validation formula that will allow (only) the word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Validation Formula

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 76
Default Validation Formula

Thanks T.Valko, but this did not work. I am setting custom validation for
cell M10. when I entered your formula as validation, excel would not allow me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <"MB" then M10 can only be Chips.

"T. Valko" wrote:

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Validation Formula

What did you enter in K10, and what did you try to enter in M10?

In what way do you think that T Valko's formula differs from your
requirement? It seems to do exactly what you asked for. Perhaps you'd
better check that you've entered the formula correctly. Did you copy and
paste, or did you try to retype it?

Note that if you enter a valid entry in M10 but then change K10, the M10
validation condition will not be checked again until you try to edit M10
again, so you may wish to impose a validation condition on K10 too.
--
David Biddulph

"Teddy-B" wrote in message
...
Thanks T.Valko, but this did not work. I am setting custom validation for
cell M10. when I entered your formula as validation, excel would not allow
me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <"MB" then M10 can only be Chips.

"T. Valko" wrote:

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the
word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then
the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Validation Formula

On another worksheet, create two lists, one with chips, and another with
all three items.
Name each list, e.g. ChipsList and PayList
There are naming instructions he
http://www.contextures.com/xlNames01.html

In the data validation cell, choose DataValidation
For Allow, select List
For the Source, enter the formula:
=IF($A$1="MB",PayList,ChipsList)


Teddy-B wrote:
Hi everyone. I need a validation formula that will allow (only) the word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 76
Default Validation Formula

Thanks for keeping me straight David. Also, Thanks T.Valko for the formula -
It works well.

"David Biddulph" wrote:

What did you enter in K10, and what did you try to enter in M10?

In what way do you think that T Valko's formula differs from your
requirement? It seems to do exactly what you asked for. Perhaps you'd
better check that you've entered the formula correctly. Did you copy and
paste, or did you try to retype it?

Note that if you enter a valid entry in M10 but then change K10, the M10
validation condition will not be checked again until you try to edit M10
again, so you may wish to impose a validation condition on K10 too.
--
David Biddulph

"Teddy-B" wrote in message
...
Thanks T.Valko, but this did not work. I am setting custom validation for
cell M10. when I entered your formula as validation, excel would not allow
me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <"MB" then M10 can only be Chips.

"T. Valko" wrote:

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the
word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then
the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"






  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 76
Default Validation Formula

Thanks for keeping me straight David. Thanks T. Valko for the formula. When
plugged in properly, it works well.

"David Biddulph" wrote:

What did you enter in K10, and what did you try to enter in M10?

In what way do you think that T Valko's formula differs from your
requirement? It seems to do exactly what you asked for. Perhaps you'd
better check that you've entered the formula correctly. Did you copy and
paste, or did you try to retype it?

Note that if you enter a valid entry in M10 but then change K10, the M10
validation condition will not be checked again until you try to edit M10
again, so you may wish to impose a validation condition on K10 too.
--
David Biddulph

"Teddy-B" wrote in message
...
Thanks T.Valko, but this did not work. I am setting custom validation for
cell M10. when I entered your formula as validation, excel would not allow
me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <"MB" then M10 can only be Chips.

"T. Valko" wrote:

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the
word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB", then
the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"






  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Validation Formula

Glad you got it straightened out. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Thanks for keeping me straight David. Thanks T. Valko for the formula.
When
plugged in properly, it works well.

"David Biddulph" wrote:

What did you enter in K10, and what did you try to enter in M10?

In what way do you think that T Valko's formula differs from your
requirement? It seems to do exactly what you asked for. Perhaps you'd
better check that you've entered the formula correctly. Did you copy and
paste, or did you try to retype it?

Note that if you enter a valid entry in M10 but then change K10, the M10
validation condition will not be checked again until you try to edit M10
again, so you may wish to impose a validation condition on K10 too.
--
David Biddulph

"Teddy-B" wrote in message
...
Thanks T.Valko, but this did not work. I am setting custom validation
for
cell M10. when I entered your formula as validation, excel would not
allow
me
to enter anything into the cell (M10).
I need to set the following validation requirements in cell (M10):

IF K10 = "MB" then M10 can only be Cash,Chips,or Check.
and
If K10 <"MB" then M10 can only be Chips.

"T. Valko" wrote:

Try this:

=OR(AND(K10<"MB",M10="Chips"),AND(K10="MB",OR(M10 ="Cash",M10="Check",M10="Chips")))

--
Biff
Microsoft Excel MVP


"Teddy-B" wrote in message
...
Hi everyone. I need a validation formula that will allow (only) the
word
"CHIPS" in the validation cell if cell A1<"MB". If Cell A1="MB",
then
the
words "CASH, CHIPS, or CHECK" (only) may be entered.

Please help- "Thanks"








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation with a formula LabrGuy Bob R Excel Worksheet Functions 7 July 2nd 07 11:04 PM
Validation Formula Teddy-B Excel Discussion (Misc queries) 3 December 28th 06 09:19 PM
Validation Formula cathyds1 Excel Discussion (Misc queries) 5 November 28th 06 07:54 PM
formula using data validation Tuttamay77 Excel Discussion (Misc queries) 5 April 4th 06 10:44 AM
need cell validation formula help adear11 Excel Discussion (Misc queries) 0 November 9th 05 06:01 PM


All times are GMT +1. The time now is 08:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"