A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Validate entry based on another cell value



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2009, 06:06 PM posted to microsoft.public.excel.misc
Dearoledad
external usenet poster
 
Posts: 10
Default Validate entry based on another cell value

How do I set up validation of data entry in cell B1 depending on the value
entered in cell A1? Valid entries for B1 are - blank if A1 1 or N or E if
column A1 zero.
  #2  
Old May 15th, 2009, 06:38 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Validate entry based on another cell value

One thing to note, you can't force a user to either leave a cell empty or
enter something. There are little things you can do to try to coax them to
follow those rules but in the end the user has all the power. ( the ON/OFF
switch!)

So, try this:

Select cell B1
Goto the menu DataValidation
AllowCustom
Formula:

=AND(COUNT(A1),A10,OR(B1="N",B1="E"))

Uncheck the "Ignore blank" checkbox.
If you want custom messages to appear select the Input Message and/or Error
Alert tabs and fill in the info.
OK out

--
Biff
Microsoft Excel MVP


"Dearoledad" wrote in message
...
How do I set up validation of data entry in cell B1 depending on the value
entered in cell A1? Valid entries for B1 are - blank if A1 1 or N or E
if
column A1 zero.



  #3  
Old May 15th, 2009, 09:56 PM posted to microsoft.public.excel.misc
Dearoledad
external usenet poster
 
Posts: 10
Default Validate entry based on another cell value

Thanks, Biff. It worked perfectly. As for a user not entering anything, I
have devised a gorella that reaches out of the screen and shakes the user if
they skip the field when they should be entering data.

Thanks again.

"T. Valko" wrote:

One thing to note, you can't force a user to either leave a cell empty or
enter something. There are little things you can do to try to coax them to
follow those rules but in the end the user has all the power. ( the ON/OFF
switch!)

So, try this:

Select cell B1
Goto the menu DataValidation
AllowCustom
Formula:

=AND(COUNT(A1),A10,OR(B1="N",B1="E"))

Uncheck the "Ignore blank" checkbox.
If you want custom messages to appear select the Input Message and/or Error
Alert tabs and fill in the info.
OK out

--
Biff
Microsoft Excel MVP


"Dearoledad" wrote in message
...
How do I set up validation of data entry in cell B1 depending on the value
entered in cell A1? Valid entries for B1 are - blank if A1 1 or N or E
if
column A1 zero.




  #4  
Old May 15th, 2009, 10:47 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Validate entry based on another cell value

a gorella that reaches out of the screen and shakes the user

I like that idea!

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dearoledad" wrote in message
...
Thanks, Biff. It worked perfectly. As for a user not entering anything, I
have devised a gorella that reaches out of the screen and shakes the user
if
they skip the field when they should be entering data.

Thanks again.

"T. Valko" wrote:

One thing to note, you can't force a user to either leave a cell empty or
enter something. There are little things you can do to try to coax them
to
follow those rules but in the end the user has all the power. ( the
ON/OFF
switch!)

So, try this:

Select cell B1
Goto the menu DataValidation
AllowCustom
Formula:

=AND(COUNT(A1),A10,OR(B1="N",B1="E"))

Uncheck the "Ignore blank" checkbox.
If you want custom messages to appear select the Input Message and/or
Error
Alert tabs and fill in the info.
OK out

--
Biff
Microsoft Excel MVP


"Dearoledad" wrote in message
...
How do I set up validation of data entry in cell B1 depending on the
value
entered in cell A1? Valid entries for B1 are - blank if A1 1 or N or
E
if
column A1 zero.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:00 AM.


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