How do I stop entry in one cell if another cell is occupied??

[Gazz_85]

Hi,

I have a spreadsheet that is being used to updated and display jobs for
production. I have two colums one for short term jobs and one for long term
jobs. The value for each cell is validated and only anything from a drop down
list can be selected. So what I need to do now is make sure if a value is
selected for short term, nothing can be entered in the long term colum...as a
job can only be either long or short term not both.

Any help????

 

[Stefi]

Custom Data validation with formulae:
Select column A: =ISBLANK(B1)
Select column B: =ISBLANK(A1)

Regards,
Stefi

„Gazz_85†ezt írta:

 

[Gazz_85]

Thanks, that works well for stopping input into the cell but it gets rid of
my list validation is there any way to do both, or another way around it.

 

[Gazz_85]

Thanks,

Works well to stop input, but removes my list validation. is there anyway to
do both, or another way around it.

 

[Gazz_85]

Thanks,

Works well to stop input, but removes my list validation. is there anyway to
do both, or another way around it.

 

[Stefi]

=AND(ISBLANK(B1),yourformula)

Regards,
Stefi

„Gazz_85†ezt írta:

 

[Gazz_85]

Thanks, ive tried it and cant get it to work. my list is situation in the
following fields BF3:BF7

So this is my formula as you suggested. however same thing happens it either
removes my list, are gives me an error.

any more suggestions???

=AND(ISBLANK(D3),$BF$3:$BF$7)

 

[Stefi]

Sorry, I didn't notice that you already have a validation list, you can't
have two validations of different type. You have to use a Worksheet_Change
event like this to make a workaround:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <= 2 Then 'A:B columns
othercol = IIf(Target.Column = 1, 2, 1)
If Not IsEmpty(Cells(Target.Row, othercol)) Then
MsgBox "Other column is not empty!", vbOKOnly, "Invalid input!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

NB! It works only with columns A and B, adjust it to your live columns!
Post if you need help to install it!

Regards,
Stefi

„Gazz_85†ezt írta:

 

[Gazz_85]

Brilliant thanks for that, works well for the columns a & b.

I need it to work so that it work for column c &d so which bit would i
change??

also i need to do the same for columns j&k, columns p&q, and columns v&w can
i just replicate this code or do i need something a little more complex?

also i just need it to validate seperate groups ie. c&d,j&k,p&q, and v&w so
if theres a value in c you CAN still put a value in j,ect but not in d....if
you know what i mean??

cheers,

much appreciated help!!

 

[Stefi]

I need it to work so that it work for column c &d so which bit would i
change??
also i need to do the same for columns j&k, columns p&q, and columns v&w can
i just replicate this code or do i need something a little more complex?

In the same worksheet?

also i just need it to validate seperate groups ie. c&d,j&k,p&q, and v&w so
if theres a value in c you CAN still put a value in j,ect but not in d....if
you know what i mean??

Do you mean "... but not in d, k, q, w"?

Stefi

 

[Gazz_85]

the worksheet is in 4 sections - representing the 4 different work areas we
have

Section 1 - columns C - I

Section 2 - columns J - O

Section 3 - columns P - U

Section 4 - columns V - Z

each section has the option of long or short term jobs with then the
validation of a drop down list containing types of job. so for each section i
need a seperate validation so for example in:

section 1 either column c or d

section 2 either column j or k

section 3 either column p or q

section 4 either column v or w

but you would still be able to enter in other sections if you entered in C,
but only not in D. if this makes sense???

Cheers

 

[Stefi]

Private Sub Worksheet_Change(ByVal Target As Range)
colpair1 = Array("C", "D")
colpair2 = Array("J", "K")
colpair3 = Array("P", "Q")
colpair4 = Array("V", "W")
If Not (Intersect(Target, Columns(colpair1(0) & ":" & colpair1(1))) Is
Nothing _
And Intersect(Target, Columns(colpair2(0) & ":" & colpair2(1))) Is
Nothing _
And Intersect(Target, Columns(colpair3(0) & ":" & colpair3(1))) Is
Nothing _
And Intersect(Target, Columns(colpair4(0) & ":" & colpair4(1))) Is
Nothing) Then
colshift = IIf(Target.Column = Range(colpair1(0) & 1).Column Or _
Target.Column = Range(colpair2(0) & 1).Column Or _
Target.Column = Range(colpair3(0) & 1).Column Or _
Target.Column = Range(colpair4(0) & 1).Column, 1, -1)
othercol = Target.Column + colshift
If Not IsEmpty(Cells(Target.Row, othercol)) Then
MsgBox "Other column is not empty!", vbOKOnly, "Invalid input!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Regards,
Stefi


„Gazz_85†ezt írta:

 

[Gazz_85]

Thanks for the help,

sorry to be a pain but i'm getting a compile error: syntax error.

the bit it doesn't like is:

If Not (Intersect(Target, Columns(colpair1(0) & ":" & colpair1(1))) Is
Nothing _
And Intersect(Target, Columns(colpair2(0) & ":" & colpair2(1))) Is
Nothing _
And Intersect(Target, Columns(colpair3(0) & ":" & colpair3(1))) Is
Nothing _
And Intersect(Target, Columns(colpair4(0) & ":" & colpair4(1))) Is
Nothing) Then

any ideas???

cheers

 

[Stefi]

This is a bad joke of this forums text editor: in case of long lines it
splits them up therefore copy/pasted them back into the VBA editor one VBA
line are split up in two without a line-continuation mark (" _" that's a
space and an underscore) at the end of the first part. In this case move all
Is Nothing _
to the end of the previous lines like follows:

If Not (Intersect(...) Is Nothing _
And Intersect(...) Is Nothing _
And Intersect(...) Is Nothing _
And Intersect(...) Is Nothing) Then

Regards,
Stefi


„Gazz_85†ezt írta:

 

[Gazz_85]

Cheers,

Works like a dream, your a real life saver!!

very much appreciated thanks for all yopur help

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi
Clicking the YES button will be appreciated.


„Gazz_85†ezt írta: