Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell References | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |