Mandatory cell

[Guest]

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.

 

[Guest]

One way ..
In B2: =IF(LEN(A1)<>0,"<< Complete A2 !!","")

 

[Guest]

Hi Max,

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

Jeff...

 

[Max]

I'm out. Pl hang around awhile for responders conversant in vba to chime in
here.

 

[Bob Phillips]

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)

 

[Earl Kiosterud]

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 !!","")

 

[Guest]

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

 

[Guest]

--
*****
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 format>pattern>red
copy A2, paste format to all cells within row field 2...,e.g. B2:AB2

regards

 

[Guest]

Driller, this is useful. Thanks a lot.

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

hi jeff,
u mean

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 format>pattern>red
copy A2, paste format to all cells within row field 2...,e.g. B2:AB2

regards

 

[Guest]

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

 

[Max]

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]

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.

---

 

[Guest]

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

 

[Max]

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.

---