Mandatory cell
 

Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

One way ..
In B2: =IF(LEN(A1)<0,"<< Complete A2 !!","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

Hi Max,

How about VBA language to check for this and pop-up box to indicate missing
entry ?

Jeff...

"Max" wrote:

One way ..
In B2: =IF(LEN(A1)<0,"<< Complete A2 !!","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

I'm out. Pl hang around awhile for responders conversant in vba to chime in
here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote in message
...
Hi Max,

How about VBA language to check for this and pop-up box to indicate
missing
entry ?

Jeff...

Mandatory cell
 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, 1).Value = "" Then
MsgBox "Adjacent cell is empty"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jeff" wrote in message
...
Hi Max,

How about VBA language to check for this and pop-up box to indicate
missing
entry ?

Jeff...

"Max" wrote:

One way ..
In B2: =IF(LEN(A1)<0,"<< Complete A2 !!","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Hi,

I like to know how do I set up in excel to setup a field to be a
mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt
error
message.

Mandatory cell
 

One problem with this is that it will give the message even when A2 has something in it.
How about:

=IF(AND(A1<"",A2=""),"<< Complete A2 !!","")
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Max" wrote in message
...
One way ..
In B2: =IF(LEN(A1)<0,"<< Complete A2 !!","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

Hi,

Another way would be to use Data/Validation. In a blank cell say N1 you
would type =AND(A1<0,B4=""), this will result in a TRUE or FALSE statement.
Next select all your input cells and goto Data/Validation, select "Custom"
from the list and type =$N$1=FALSE, next click on the "Error Alert" tab and
type a small message to let people know what they should do or why they
received the alert message. So if A1 has data and B1 is blank then an error
message will pop up when trying to input data into the other input cells.


HTH
Jean-Guy

"Jeff" wrote:

Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

--
*****
birds of the same feather flock together..

hi jeff,
u mean
For example: If A1 has data, A2 can not be a blank field and prompt error
message.

instead of a message maybe u can work it out with colors..
e.g RED cell means it is a blank field/cell beside the data.

on row fields 2
u can try to insert on A2 a conditional formatting like formula is :
=and(A1<"",A2="")
select formatpatternred
copy A2, paste format to all cells within row field 2...,e.g. B2:AB2

regards

"Jeff" wrote:

Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

Driller, this is useful. Thanks a lot.


"driller" wrote:


--
*****
birds of the same feather flock together..

hi jeff,
u mean
For example: If A1 has data, A2 can not be a blank field and prompt error
message.

instead of a message maybe u can work it out with colors..
e.g RED cell means it is a blank field/cell beside the data.

on row fields 2
u can try to insert on A2 a conditional formatting like formula is :
=and(A1<"",A2="")
select formatpatternred
copy A2, paste format to all cells within row field 2...,e.g. B2:AB2

regards

"Jeff" wrote:

Hi,

I like to know how do I set up in excel to setup a field to be a mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt error
message.

Mandatory cell
 

Bob

I inserted the program in excel but it's not showing mssg. Do I need to
press any funtion key to make it run ?

Rgds..Jeff

"Bob Phillips" wrote:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, 1).Value = "" Then
MsgBox "Adjacent cell is empty"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jeff" wrote in message
...
Hi Max,

How about VBA language to check for this and pop-up box to indicate
missing
entry ?

Jeff...

"Max" wrote:

One way ..
In B2: =IF(LEN(A1)<0,"<< Complete A2 !!","")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
Hi,

I like to know how do I set up in excel to setup a field to be a
mandatory
field if the field beside it has data ?

For example: If A1 has data, A2 can not be a blank field and prompt
error
message.

Mandatory cell
 

Depends on the interp, and the alert phrase <g

Perhaps: "<< Complete A2 !!" in the earlier alerts better as:
"<< Enter / Re-enter A2 !!"
which then caters for giving a message to re-enter A2
even when A2 has something in it (prior to entry in A1).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Earl Kiosterud" wrote in message
...
One problem with this is that it will give the message even when A2 has
something in it. How about:

=IF(AND(A1<"",A2=""),"<< Complete A2 !!","")
--
Earl Kiosterud
www.smokeylake.com

Mandatory cell
 

Did you install it as prescribed by Bob, re his steps:

... To do this, right-click on the sheet tab,
select 'the View Code option from the menu,
and paste the code in.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote in message
...
Bob

I inserted the program in excel but it's not showing mssg. Do I need to
press any funtion key to make it run ?

Rgds..Jeff

Mandatory cell
 

Yes, i did. I will check with my friend on this offline. Thanks.

"Max" wrote:

Did you install it as prescribed by Bob, re his steps:

... To do this, right-click on the sheet tab,
select 'the View Code option from the menu,
and paste the code in.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote in message
...
Bob

I inserted the program in excel but it's not showing mssg. Do I need to
press any funtion key to make it run ?

Rgds..Jeff

Mandatory cell
 

On Feb 5, 7:45 pm, Jeff wrote:
Yes, i did. I will check with my friend on this offline. Thanks.

Pl keep all discussions online for the benefit of all. Let us know why
it didn't work for you despite your installing of it as advised.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---