Automatic Feed

[philb99]

Hi - I have 28 Support Leaders each have a Group number allocated to them from 1-28. I am trying to get an automatic feed into a column next to where once you select a support leader their group number will automatically feed into the next column.

However there will be occasions when a Support Leader will not be required and N/A entered. In the next column we have the date a SL has been assigned therefore is there anyway that when N/A has been selected an automatic N/A is entered.

I'm sure its something simple.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given alink to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[philb99]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given alink to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Dropbox

www.dropbox.com

there you go, can you now help me

 

[mumps]

It is still not clear what you want to do. Are you saying that once you enter a name in column B, you want their group number will appear in column C? If that is the case, how do we know which group number is assigned to which name? IN which column will you enter the NA?

In the next column we have the date a SL has been assigned therefore is there anyway that when N/A has been selected an automatic N/A is entered.

Please clarify this in detail using a few examples and referring to specific cells.

 

[philb99]

It is still not clear what you want to do. Are you saying that once you enter a name in column B, you want their group number will appear in column C? If that is the case, how do we know which group number is assigned to which name? IN which column will you enter the NA?

Please clarify this in detail using a few examples and referring to specific cells

The Support Contact names and their individual Group Number are recorded on a separate tab in the same spreadsheet. For example John Mumber is Group Number 1 / Partner Group Number is N/A.

Yes once a name is keyed into Column B - John Mumber, then I would like the Group number to automatically populate into Col C.

Where the Support Contact Name is Partner their Group Number would be N/A and therefore I would like N/A to be automatically populated into Col D

 

[mumps]

Could you post a sample of what the data looks like in the other sheet?

 

[philb99]

Could you post a sample of what the data looks like in the other sheet?

Thats very simple - Col A going down 28 rows with 28 names / Col B Numbered 1-28

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name of the sheet containing the Support Contact names and their individual Group Number (in red) to suit your needs. Close the code window to return to your sheet. Enter a name in column B and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range
    Select Case Target.Value
        Case "Partner"
            Target.Offset(, 1).Resize(, 2).Value = Array("Not Required", "N/A")
        Case Else
            Set fnd = Sheets("Sheet2").Range("A:A").Fid(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                Target.Offset(, 1) = fnd.Offset(, 1)
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

 

[philb99]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name of the sheet containing the Support Contact names and their individual Group Number (in red) to suit your needs. Close the code window to return to your sheet. Enter a name in column B and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range
    Select Case Target.Value
        Case "Partner"
            Target.Offset(, 1).Resize(, 2).Value = Array("Not Required", "N/A")
        Case Else
            Set fnd = Sheets("Sheet2").Range("A:A").Fid(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                Target.Offset(, 1) = fnd.Offset(, 1)
            End If
    End Select
    Application.ScreenUpdating = True
End Sub

Many thanks for providing but nothing happens when I run the code. When I View Macros in the View ribbon nothing shows but when I right Click tab and View Code, I can see the Macro but it doesn't run.

Does this code also populate the Group Number when a Support Contact has been allocated?

 

[mumps]

I tested the macro using the file you posted and it worked as you requested. Are macros enabled in your workbook? If they are, then please upload a copy of the file that isn’t working for you, de-sensitized if necessary.