I have three data cells in my table that I need to check for data. If one of
the three cell has data in it, the other two can not be filled with anything,
somekind a pop up should be good too. I hope someone understand what I mean,
and can also help me out.

With data validation perhaps. Assuming the 3 cells in question are A1 - A3,
select them and then

Data|validation|Settings|Custom

and use the formula

=COUNTA(A$1:A$3)<=1

Mike

"T.Lindholm" wrote:

> I have three data cells in my table that I need to check for data. If one of
> the three cell has data in it, the other two can not be filled with anything,
> somekind a pop up should be good too. I hope someone understand what I mean,
> and can also help me out.

I'm afraid data validation doesn't do it, because I have it already in use.
Cell 1 has values 1-2, cell 2 has values 3-4 and cell 3 has value 5. If I
insert number into cell 2, then I would not be able to input any numbers into
cells 1 and 3, and so on.

"Mike H" wrote:

> With data validation perhaps. Assuming the 3 cells in question are A1 - A3,
> select them and then
>
> Data|validation|Settings|Custom
>
> and use the formula
>
> =COUNTA(A$1:A$3)<=1
>
> Mike
>
> "T.Lindholm" wrote:
>
> > I have three data cells in my table that I need to check for data. If one of
> > the three cell has data in it, the other two can not be filled with anything,
> > somekind a pop up should be good too. I hope someone understand what I mean,
> > and can also help me out.

Try this small worksheet event mcro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
Set r = Range("A1:A3")
If Intersect(Target, r) Is Nothing Then Exit Sub
v = Target.Value
Application.EnableEvents = False
r.Clear
Target.Value = v
Application.EnableEvents = True
End Sub

If you enter a value in the range A1 thru A3, the other cells in that range
will be cleared.
--
Gary''s Student - gsnu200747


"T.Lindholm" wrote:

> I have three data cells in my table that I need to check for data. If one of
> the three cell has data in it, the other two can not be filled with anything,
> somekind a pop up should be good too. I hope someone understand what I mean,
> and can also help me out.

I couldn't get this to work at all in Excel 2000 on Windows XP pro.

Dan

On Sep 26, 3:10 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Try this small worksheet event mcro:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim v As Variant
> Set r = Range("A1:A3")
> If Intersect(Target, r) Is Nothing Then Exit Sub
> v = Target.Value
> Application.EnableEvents = False
> r.Clear
> Target.Value = v
> Application.EnableEvents = True
> End Sub
>
> If you enter a value in the range A1 thru A3, the other cells in that range
> will be cleared.
> --
> Gary''s Student - gsnu200747
>
> "T.Lindholm" wrote:
> > I have three data cells in my table that I need to check for data. If one of
> > the three cell has data in it, the other two can not be filled with anything,
> > somekind a pop up should be good too. I hope someone understand what I mean,
> > and can also help me out.

Where did you place the code?

It is worksheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 27 Sep 2007 23:11:08 -0000, dan dungan <(E-Mail Removed)> wrote:

>I couldn't get this to work at all in Excel 2000 on Windows XP pro.
>
>Dan
>
>On Sep 26, 3:10 am, Gary''s Student
><GarysStud...@discussions.microsoft.com> wrote:
>> Try this small worksheet event mcro:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim v As Variant
>> Set r = Range("A1:A3")
>> If Intersect(Target, r) Is Nothing Then Exit Sub
>> v = Target.Value
>> Application.EnableEvents = False
>> r.Clear
>> Target.Value = v
>> Application.EnableEvents = True
>> End Sub
>>
>> If you enter a value in the range A1 thru A3, the other cells in that range
>> will be cleared.
>> --
>> Gary''s Student - gsnu200747
>>
>> "T.Lindholm" wrote:
>> > I have three data cells in my table that I need to check for data. If one of
>> > the three cell has data in it, the other two can not be filled with anything,
>> > somekind a pop up should be good too. I hope someone understand what I mean,
>> > and can also help me out.
>

Hi Gord,

Running the procedure returned the Compile error, "variable not
defined". So I dimmed r as range.

Then it worked fine.

I guess that because use Option Explicit

Dan


On Sep 27, 4:54 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Where did you place the code?
>
> It is worksheet event code. Right-click on the sheet tab and "View Code".
>
> Copy/paste into that sheet module.
>
> Gord Dibben MS Excel MVP
>
> On Thu, 27 Sep 2007 23:11:08 -0000, dan dungan <stagerob...@yahoo.com> wrote:
> >I couldn't get this to work at all in Excel 2000 on Windows XP pro.
>
> >Dan
>
> >On Sep 26, 3:10 am, Gary''s Student
> ><GarysStud...@discussions.microsoft.com> wrote:
> >> Try this small worksheet event mcro:
>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Dim v As Variant
> >> Set r = Range("A1:A3")
> >> If Intersect(Target, r) Is Nothing Then Exit Sub
> >> v = Target.Value
> >> Application.EnableEvents = False
> >> r.Clear
> >> Target.Value = v
> >> Application.EnableEvents = True
> >> End Sub
>
> >> If you enter a value in the range A1 thru A3, the other cells in that range
> >> will be cleared.
> >> --
> >> Gary''s Student - gsnu200747
>
> >> "T.Lindholm" wrote:
> >> > I have three data cells in my table that I need to check for data. If one of
> >> > the three cell has data in it, the other two can not be filled with anything,
> >> > somekind a pop up should be good too. I hope someone understand what I mean,
> >> > and can also help me out.

OK, I got this event macro to work, but now I have another problem. I need to
run the same macro to multiple others rows, with the same functions.
How/Where should I place the macro/code, do I have to type it for each row
separately or what?

"Gary''s Student" wrote:

> Try this small worksheet event mcro:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim v As Variant
> Set r = Range("A1:A3")
> If Intersect(Target, r) Is Nothing Then Exit Sub
> v = Target.Value
> Application.EnableEvents = False
> r.Clear
> Target.Value = v
> Application.EnableEvents = True
> End Sub
>
> If you enter a value in the range A1 thru A3, the other cells in that range
> will be cleared.
> --
> Gary''s Student - gsnu200747
>
>
> "T.Lindholm" wrote:
>
> > I have three data cells in my table that I need to check for data. If one of
> > the three cell has data in it, the other two can not be filled with anything,
> > somekind a pop up should be good too. I hope someone understand what I mean,
> > and can also help me out.

If you have several of these triplets, how are they organized ??

We can alter/expand the code, but we need to know where the triplets are.
--
Gary''s Student - gsnu200748


"T.Lindholm" wrote:

> OK, I got this event macro to work, but now I have another problem. I need to
> run the same macro to multiple others rows, with the same functions.
> How/Where should I place the macro/code, do I have to type it for each row
> separately or what?
>
> "Gary''s Student" wrote:
>
> > Try this small worksheet event mcro:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim v As Variant
> > Set r = Range("A1:A3")
> > If Intersect(Target, r) Is Nothing Then Exit Sub
> > v = Target.Value
> > Application.EnableEvents = False
> > r.Clear
> > Target.Value = v
> > Application.EnableEvents = True
> > End Sub
> >
> > If you enter a value in the range A1 thru A3, the other cells in that range
> > will be cleared.
> > --
> > Gary''s Student - gsnu200747
> >
> >
> > "T.Lindholm" wrote:
> >
> > > I have three data cells in my table that I need to check for data. If one of
> > > the three cell has data in it, the other two can not be filled with anything,
> > > somekind a pop up should be good too. I hope someone understand what I mean,
> > > and can also help me out.

The triplets are organized like this:
I N S
13 13 13
17 17 17
21 21 21
25 25 25
30 30 30
37 37 37
40 40 40
43 43 43
46 46 46
52 52 52
56 56 56
60 60 60
64 64 64
67 67 67
73 73 73
76 76 76
81 81 81
85 85 85
88 88 88
96 96 96
100 100 100
109 109 109
112 112 112
116 116 116
120 120 120

Phew, let me know how to go from here.
"Gary''s Student" wrote:

> If you have several of these triplets, how are they organized ??
>
> We can alter/expand the code, but we need to know where the triplets are.
> --
> Gary''s Student - gsnu200748
>
>
> "T.Lindholm" wrote:
>
> > OK, I got this event macro to work, but now I have another problem. I need to
> > run the same macro to multiple others rows, with the same functions.
> > How/Where should I place the macro/code, do I have to type it for each row
> > separately or what?
> >
> > "Gary''s Student" wrote:
> >
> > > Try this small worksheet event mcro:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim v As Variant
> > > Set r = Range("A1:A3")
> > > If Intersect(Target, r) Is Nothing Then Exit Sub
> > > v = Target.Value
> > > Application.EnableEvents = False
> > > r.Clear
> > > Target.Value = v
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > If you enter a value in the range A1 thru A3, the other cells in that range
> > > will be cleared.
> > > --
> > > Gary''s Student - gsnu200747
> > >
> > >
> > > "T.Lindholm" wrote:
> > >
> > > > I have three data cells in my table that I need to check for data. If one of
> > > > the three cell has data in it, the other two can not be filled with anything,
> > > > somekind a pop up should be good too. I hope someone understand what I mean,
> > > > and can also help me out.