IF statement problem

[GK039]

Hi,

Is this possible?:

If I enter 1 in Cell A1 then Cell B1 = Branch1 and vice versa? That is, if I enter Branch1 in Cell B1 then A1 = 1. I want to populate results based on what value is enterd in cells A1 or B1, but i want to work both ways around.

Thanks

George

 

[Peter_SSs]

This is not possible with formulas but could be achieved with a macro. Would that be acceptable? (Sheets users would have to enable macros for the code to work on the sheet)

 

[GK039]

Hi Peter,

Yes this would be acceptable. If you could please help me with the code

George

This is not possible with formulas but could be achieved with a macro. Would that be acceptable? (Sheets users would have to enable macros for the code to work on the sheet)

 

[Peter_SSs]

This could require some tweaks depending on just what might be entered on the sheet and what consequent results you want, but give this a try in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try some data entry.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1:B1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Range("A1").Value = 1 <SPAN style="color:#00007F">Then</SPAN><br>            Range("B1").Value = "Branch 1"<br>        <SPAN style="color:#00007F">ElseIf</SPAN> Range("B1").Value = "Branch 1" <SPAN style="color:#00007F">Then</SPAN><br>            Range("A1").Value = 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[GK039]

Hi Peter,

It works but it doesn't allow me to edit either cell. It's like the cells have frosen and accept only these two values. 1 in cell A1 and Branch 1 in cell B1. I cannot even delete the values in the cells

George

This could require some tweaks depending on just what might be entered on the sheet and what consequent results you want, but give this a try in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try some data entry.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
Application.EnableEvents = False
If Range("A1").Value = 1 Then
Range("B1").Value = "Branch 1"
ElseIf Range("B1").Value = "Branch 1" Then
Range("A1").Value = 1
End If
Application.EnableEvents = True
End If
End Sub

 

[Peter_SSs]

As I mentioned, it depends on just what might be entered and what consequent action you want.

With that previous code, you should be able to select both cells together and delete the values.

Otherwise see if this suits better.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1:B1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Target<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .Address(0, 0)<br>                <SPAN style="color:#00007F">Case</SPAN> "A1"<br>                    <SPAN style="color:#00007F">If</SPAN> .Value = 1 Then _<br>                        .Offset(, 1).Value = "Branch 1"<br>                <SPAN style="color:#00007F">Case</SPAN> "B1"<br>                    <SPAN style="color:#00007F">If</SPAN> .Value = "Branch 1" Then _<br>                        .Offset(, -1).Value = 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[GK039]

Thank you very much Peter

It works great

George

As I mentioned, it depends on just what might be entered and what consequent action you want.

With that previous code, you should be able to select both cells together and delete the values.

Otherwise see if this suits better.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
Application.EnableEvents = False
With Target
Select Case .Address(0, 0)
Case "A1"
If .Value = 1 Then _
.Offset(, 1).Value = "Branch 1"
Case "B1"
If .Value = "Branch 1" Then _
.Offset(, -1).Value = 1
End Select
End With
Application.EnableEvents = True
End If
End Sub