I would like to enter the letter "Y" in cell A1, and if I enter "Y" in B1,
the "Y" is automatically removed from A1. And if I enter "Y" in C1, "Y" is
cleared from B1.
Is there a formula that can achieve this and allow me to enter data in the
cell without removing the formula?

Please help,
And Thanks,
Digital2k

Hi Digital2k,

I think you will have to use a WorksheetChange Event Procedure to
achieve that.

Something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Range("B1") = "Y" Then Range("A1") = ""
If Range("C1") = "Y" Then Range("B1") = ""
End If
Application.EnableEvents = True
End Sub

This code has to be pasted into the Code Module of the affected
worksheet. If you copy the code then right-click your worksheet's sheet
tab, then choose "View Code" from the popup menu, you can paste it into
the code module that appears.

Your security level will have to be medium (Tools|Macro|Security...
click "Medium" then Save and Close workbook and Re-Open and click
"Enable Macros" on the dialog that appears.

Ken Johnson

post your formula

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Digital2k" <(E-Mail Removed)> wrote in message
news:y-(E-Mail Removed)...
>I would like to enter the letter "Y" in cell A1, and if I enter "Y" in B1,
>the "Y" is automatically removed from A1. And if I enter "Y" in C1, "Y" is
>cleared from B1.
> Is there a formula that can achieve this and allow me to enter data in the
> cell without removing the formula?
>
> Please help,
> And Thanks,
> Digital2k
>

Thank you Ken,
I did exactly as you described but it did not work for me.
I should have given you more info.
I have a worksheet with 100 rows from 7-106 that I want to include and three
columns S, AC, AI that I want to use for this formula or macro.( I have
other data in the other cells)
If S7 has a "Y" and I insert a "Y" in AC7 I would like "Y" from S7 to be
removed. If I insert a Y in AI7 I would like AC7 to be removed.
In other words There should only be one y in either cell mentioned for that
row.
I would like all 100 rows to have the same function but not effect other
rows. If I insert a y in cell AI7 I want S7 or AC7 to have a y removed, S8
or AC8 should not be effected unless I insert y in that row.
I hope I made myself a little clearer. Is this even possible to do?
Thanks so much in advice
Digital2k

"Ken Johnson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Digital2k,
>
> I think you will have to use a WorksheetChange Event Procedure to
> achieve that.
>
> Something like...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
> If Range("B1") = "Y" Then Range("A1") = ""
> If Range("C1") = "Y" Then Range("B1") = ""
> End If
> Application.EnableEvents = True
> End Sub
>
> This code has to be pasted into the Code Module of the affected
> worksheet. If you copy the code then right-click your worksheet's sheet
> tab, then choose "View Code" from the popup menu, you can paste it into
> the code module that appears.
>
> Your security level will have to be medium (Tools|Macro|Security...
> click "Medium" then Save and Close workbook and Re-Open and click
> "Enable Macros" on the dialog that appears.
>
> Ken Johnson
>

right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Or Target.Row > 106 Then Exit Sub
ar = Target.Row
If UCase(Cells(ar, "ac")) = "Y" Then Cells(ar, "s") = ""
If UCase(Cells(ar, "ai")) = "Y" Then Cells(ar, "ac") = ""
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Digital2k" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank you Ken,
> I did exactly as you described but it did not work for me.
> I should have given you more info.
> I have a worksheet with 100 rows from 7-106 that I want to include and
> three columns S, AC, AI that I want to use for this formula or macro.( I
> have other data in the other cells)
> If S7 has a "Y" and I insert a "Y" in AC7 I would like "Y" from S7 to be
> removed. If I insert a Y in AI7 I would like AC7 to be removed.
> In other words There should only be one y in either cell mentioned for
> that row.
> I would like all 100 rows to have the same function but not effect other
> rows. If I insert a y in cell AI7 I want S7 or AC7 to have a y removed, S8
> or AC8 should not be effected unless I insert y in that row.
> I hope I made myself a little clearer. Is this even possible to do?
> Thanks so much in advice
> Digital2k
>
> "Ken Johnson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Digital2k,
>>
>> I think you will have to use a WorksheetChange Event Procedure to
>> achieve that.
>>
>> Something like...
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
>> With Application
>> .ScreenUpdating = False
>> .EnableEvents = False
>> End With
>> If Range("B1") = "Y" Then Range("A1") = ""
>> If Range("C1") = "Y" Then Range("B1") = ""
>> End If
>> Application.EnableEvents = True
>> End Sub
>>
>> This code has to be pasted into the Code Module of the affected
>> worksheet. If you copy the code then right-click your worksheet's sheet
>> tab, then choose "View Code" from the popup menu, you can paste it into
>> the code module that appears.
>>
>> Your security level will have to be medium (Tools|Macro|Security...
>> click "Medium" then Save and Close workbook and Re-Open and click
>> "Enable Macros" on the dialog that appears.
>>
>> Ken Johnson
>>
>
>

Hi Digital2k,

Try this which will allow a "Y" in only one of the three cell (S, AC or
AI) for each of the rows from row 7 down to row 106



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Y" Then
If Not Intersect(Range("S7:S106,AC7:AC106,AI7:AI106"), _
Target) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
Select Case Target.Column

Case Range("S:S").Column
Cells(Target.Row, Range("AC:AC").Column).ClearContents
Cells(Target.Row, Range("AI:AI").Column).ClearContents

Case Range("AC:AC").Column
Cells(Target.Row, Range("S:S").Column).ClearContents
Cells(Target.Row, Range("AI:AI").Column).ClearContents

Case Range("AI:AI").Column
Cells(Target.Row, Range("S:S").Column).ClearContents
Cells(Target.Row, Range("AC:AC").Column).ClearContents

End Select
End If
Application.EnableEvents = True
End If
Exit Sub
ERRORHANDLER: Application.EnableEvents = True
End Sub

If you want to remove the case sensitivity so that Y or y will work
then change the second line to...

If Ucase(Target.Value) = "Y" Then


Ken Johnson

Ken, You are the MAN!!
That was brilliant!
Thank you very much that worked great!
Digital2k




"Ken Johnson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Digital2k,
>
> Try this which will allow a "Y" in only one of the three cell (S, AC or
> AI) for each of the rows from row 7 down to row 106
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Value = "Y" Then
> If Not Intersect(Range("S7:S106,AC7:AC106,AI7:AI106"), _
> Target) Is Nothing Then
> Application.EnableEvents = False
> On Error GoTo ERRORHANDLER
> Select Case Target.Column
>
> Case Range("S:S").Column
> Cells(Target.Row, Range("AC:AC").Column).ClearContents
> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>
> Case Range("AC:AC").Column
> Cells(Target.Row, Range("S:S").Column).ClearContents
> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>
> Case Range("AI:AI").Column
> Cells(Target.Row, Range("S:S").Column).ClearContents
> Cells(Target.Row, Range("AC:AC").Column).ClearContents
>
> End Select
> End If
> Application.EnableEvents = True
> End If
> Exit Sub
> ERRORHANDLER: Application.EnableEvents = True
> End Sub
>
> If you want to remove the case sensitivity so that Y or y will work
> then change the second line to...
>
> If Ucase(Target.Value) = "Y" Then
>
>
> Ken Johnson
>

Thanks again,
One more thing, When I clear contents on a row or entire sheet, I get a
run-time error:
Run-time error '13':
type mismatch
When I debug, the second line in your formula is highlighted.
If Ucase(Target.Value) = "Y" Then
Any clues on this issue?
Digital2k



"Ken Johnson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Digital2k,
>
> Try this which will allow a "Y" in only one of the three cell (S, AC or
> AI) for each of the rows from row 7 down to row 106
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Value = "Y" Then
> If Not Intersect(Range("S7:S106,AC7:AC106,AI7:AI106"), _
> Target) Is Nothing Then
> Application.EnableEvents = False
> On Error GoTo ERRORHANDLER
> Select Case Target.Column
>
> Case Range("S:S").Column
> Cells(Target.Row, Range("AC:AC").Column).ClearContents
> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>
> Case Range("AC:AC").Column
> Cells(Target.Row, Range("S:S").Column).ClearContents
> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>
> Case Range("AI:AI").Column
> Cells(Target.Row, Range("S:S").Column).ClearContents
> Cells(Target.Row, Range("AC:AC").Column).ClearContents
>
> End Select
> End If
> Application.EnableEvents = True
> End If
> Exit Sub
> ERRORHANDLER: Application.EnableEvents = True
> End Sub
>
> If you want to remove the case sensitivity so that Y or y will work
> then change the second line to...
>
> If Ucase(Target.Value) = "Y" Then
>
>
> Ken Johnson
>

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Or Target.Row > 100 Then Exit Sub
ar = Target.Row
Application.EnableEvents = False
If UCase(Cells(ar, "c")) = "Y" Then Cells(ar, "b") = ""
If UCase(Cells(ar, "d")) = "Y" Then Cells(ar, "c") = ""
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:e$(E-Mail Removed)...
> right click sheet tab>view code>insert this>modify to suit>SAVE
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Row < 7 Or Target.Row > 106 Then Exit Sub
> ar = Target.Row
> If UCase(Cells(ar, "ac")) = "Y" Then Cells(ar, "s") = ""
> If UCase(Cells(ar, "ai")) = "Y" Then Cells(ar, "ac") = ""
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Digital2k" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thank you Ken,
>> I did exactly as you described but it did not work for me.
>> I should have given you more info.
>> I have a worksheet with 100 rows from 7-106 that I want to include and
>> three columns S, AC, AI that I want to use for this formula or macro.( I
>> have other data in the other cells)
>> If S7 has a "Y" and I insert a "Y" in AC7 I would like "Y" from S7 to be
>> removed. If I insert a Y in AI7 I would like AC7 to be removed.
>> In other words There should only be one y in either cell mentioned for
>> that row.
>> I would like all 100 rows to have the same function but not effect other
>> rows. If I insert a y in cell AI7 I want S7 or AC7 to have a y removed,
>> S8 or AC8 should not be effected unless I insert y in that row.
>> I hope I made myself a little clearer. Is this even possible to do?
>> Thanks so much in advice
>> Digital2k
>>
>> "Ken Johnson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi Digital2k,
>>>
>>> I think you will have to use a WorksheetChange Event Procedure to
>>> achieve that.
>>>
>>> Something like...
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>> If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
>>> With Application
>>> .ScreenUpdating = False
>>> .EnableEvents = False
>>> End With
>>> If Range("B1") = "Y" Then Range("A1") = ""
>>> If Range("C1") = "Y" Then Range("B1") = ""
>>> End If
>>> Application.EnableEvents = True
>>> End Sub
>>>
>>> This code has to be pasted into the Code Module of the affected
>>> worksheet. If you copy the code then right-click your worksheet's sheet
>>> tab, then choose "View Code" from the popup menu, you can paste it into
>>> the code module that appears.
>>>
>>> Your security level will have to be medium (Tools|Macro|Security...
>>> click "Medium" then Save and Close workbook and Re-Open and click
>>> "Enable Macros" on the dialog that appears.
>>>
>>> Ken Johnson
>>>
>>
>>
>
>

> If Ucase(Target.Value) = "Y" Then
try
If Ucase(Target) = "Y" Then

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Digital2k" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks again,
> One more thing, When I clear contents on a row or entire sheet, I get a
> run-time error:
> Run-time error '13':
> type mismatch
> When I debug, the second line in your formula is highlighted.
> If Ucase(Target.Value) = "Y" Then
> Any clues on this issue?
> Digital2k
>
>
>
> "Ken Johnson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Digital2k,
>>
>> Try this which will allow a "Y" in only one of the three cell (S, AC or
>> AI) for each of the rows from row 7 down to row 106
>>
>>
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Value = "Y" Then
>> If Not Intersect(Range("S7:S106,AC7:AC106,AI7:AI106"), _
>> Target) Is Nothing Then
>> Application.EnableEvents = False
>> On Error GoTo ERRORHANDLER
>> Select Case Target.Column
>>
>> Case Range("S:S").Column
>> Cells(Target.Row, Range("AC:AC").Column).ClearContents
>> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>>
>> Case Range("AC:AC").Column
>> Cells(Target.Row, Range("S:S").Column).ClearContents
>> Cells(Target.Row, Range("AI:AI").Column).ClearContents
>>
>> Case Range("AI:AI").Column
>> Cells(Target.Row, Range("S:S").Column).ClearContents
>> Cells(Target.Row, Range("AC:AC").Column).ClearContents
>>
>> End Select
>> End If
>> Application.EnableEvents = True
>> End If
>> Exit Sub
>> ERRORHANDLER: Application.EnableEvents = True
>> End Sub
>>
>> If you want to remove the case sensitivity so that Y or y will work
>> then change the second line to...
>>
>> If Ucase(Target.Value) = "Y" Then
>>
>>
>> Ken Johnson
>>
>
>