Can’t skip cells, no dupes, no formulas

[XLawrence]

Hi, I have a list of 20 names in column B. Everyday I need to select 6-8 of these names and have them appear in col J without skipping cells, without duplicates and without using a formula in J. If cells get skipped it will break the formula on another sheet that is the purpose of the workbook. Also, if I have duplicates in J the other sheet will not work.

Right now I am using data validation in col J to select from the names in column B. It doesn’t prevent skipping or duplicates. It would be better for the end user to just select names in B. Then I could hide J.

I can create a checkbox for each name. Good idea?
How can I get the names selected in column B to J without skipping cells, duplicating names or putting a formula in J?

Many Thanks!

 

[Yongle]

Amended code as promised

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cel As Range, Rng As Range, r As Long
    r = 21
    Set Rng = Range("J2:J" & r)
    If Not Intersect(Target, Rng) Is Nothing Then
        Application.EnableEvents = False
        For Each Cel In Target
            If Cel = "" Then
                Range("J" & Cel.Row & ":J" & r).ClearContents
                Exit For
            Else
                If WorksheetFunction.CountIf(Rng, Cel) > 1 Then Cel.ClearContents
            End If
              
            If Cel.Offset(-1) = "" Then
                If Cel.Row > 2 Then Cel.ClearContents
            End If
        Next Cel
        Application.EnableEvents = True
    End If
End Sub

 

[jasonb75]

I have 1-20 assigned any given day - can this be modified to work with the number assigned? I tried to make it work but could not.

Do you mean that the number of names to choose (currently 8) could be anywhere from 1 to 20 (see notes on MOD below), or ore you referring to the list of names in Sheet2, column A? (see notes after next quote).

Also, could you you help me with what's going on Sheet2 A:A and Sheet2 B$2:B$21? I just copied the list of 20 names but wasn't sure that's what you did.

Sheeet2!A:A (my bad, it should have been Sheet2!B:B), simply refers to the whole column where the names are listed, many people will use the exact range, but doing so adds extra steps to other parts of the formula, making it more complicated (and less efficient) than it needs to be.

B$2:B$21 refers to the actual list of names (if you want to allow for a longer list, you can include blanks to allow for future changes but keep it realistic, don't use B$2:B$1000 if you will never enter names past B$100). If you increase the size of this range, C$2:C$21 must be adjusted as well, both ranges need to start and end in the same rows.

Blanks will only be a problem if you tick a checkbox next to an empty cell in column.

Also, your divisor is 8 for MOD - how did you decide 8?

The MOD divisor of 8 and the sumproduct check of =8 at the beginning of the formula both refer to the number names to be selected.

The edited (and corrected) formula below allows for a list of up to 50 names to choose from.

Sumproduct(....)=8 counts how many checkboxes are ticked, names will only be shown in column K if the correct amount have been chosen.

=IF(A2="","",IF(SUMPRODUCT(--Sheet2!C$2:C$50)=8,INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!B$2:B$50)/Sheet2!C$2:C$50,MOD(ROWS(K$2:K2)-1,8)+1)),""))

If you need to vary the number of names chosen then you could specify how many in a cell instead of writing it into the formula.

 

[XLawrence]

Amended code as promised

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cel As Range, Rng As Range, r As Long
    r = 21
    Set Rng = Range("J2:J" & r)
    If Not Intersect(Target, Rng) Is Nothing Then
        Application.EnableEvents = False
        For Each Cel In Target
            If Cel = "" Then
                Range("J" & Cel.Row & ":J" & r).ClearContents
                Exit For
            Else
                If WorksheetFunction.CountIf(Rng, Cel) > 1 Then Cel.ClearContents
            End If
             
            If Cel.Offset(-1) = "" Then
                If Cel.Row > 2 Then Cel.ClearContents
            End If
        Next Cel
        Application.EnableEvents = True
    End If
End Sub

This is working good. Nicely done. Now I just need to incorporate a Vlookup so I can slect the Name but use the UserID on Sheet1. First time I am bringing this up because I thought I would be able to do but I am stumped again. This is what I have but I need it to work when the name is selcted.

Sub IDLookup()

Dim i As Long

For i = 2 To 21

Worksheets("Sheet2").Cells(i, 14).Value = Application.WorksheetFunctions _
.Vlookup(Worksheets("Sheet2").Cells(i, 13).Value, Worksheets("Sheet2").Range("P:Q"), 2, 0)

Next i
    

End Sub