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]

So ...
each names can only be selected ONCE
the names chosen must be in consecutive cells in column B

Are the names in B2:B21 ?
Are selected values placed on the same row

Is this what you want ?

 

[jasonb75]

Eliminating everything that you don't want to use, you're only left with vba.

Why no formulas in column J?

Formulas don't usually cause problems, but badly written formulas often do.

 

[XLawrence]

Thank you Yongle and Jason for the swift reply,

The names need to be starting at J2 and look like this... highlighting just to show which were selected. Not sure how to select in B and have them appear in J in order.

On Sheet1 Col K is a formula which assigns those from J on Sheet2 in a round-robin. So if there is a task in Sheet1 A1 then the person in Sheet2 J2 gets that task. That continues for as many staff in J. Then it starts over from J2. If I put a formula in J - my formula in Sheet1K picks up the formula and I get zeros (0).

 

[XLawrence]

Wanted to post the formula in Sheet1K as well.

=IF(A2="","",IF(ISBLANK(INDIRECT("Sheet2!J"&ROW(A2))),INDIRECT("Sheet1!K"&(ROWS($K$2:K2)-(COUNTA(Sheet2!J:J)-2))),Sheet2!J2))

 

[jasonb75]

You could do it with checkboxes, that is probably the safest way to achieve the result that you need. If done correctly you could even eliminate column J from the process entirely.

My way would be to link each checkbox to the corresponding cell in column C, then use the formula below in K2 of sheet 1 and fill down,

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

The bold parts at the start and end are not strictly necessary, but are there to check the selection, it will only show the names if 8 have been selected, any more or any less will leave the list empty.

 

[Yongle]

VBA option
- a duplicate name selected from dropdown in J is rejected
- value also rejected if cell above is empty

Place code in sheet2 code window
(right click on sheet2 tab\ select View Code \ paste code into window that opens)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cel As Range, Rng As Range
    Set Rng = Range("J2:J21")
    If Not Intersect(Target, Rng) Is Nothing Then
        For Each Cel In Target
            If Cel.Value <> "" Then
                If Cel.Offset(-1) = "" Or WorksheetFunction.CountIf(Rng, Cel) > 1 Then Cel.ClearContents
            End If
        Next Cel
    End If
End Sub

 

[XLawrence]

You could do it with checkboxes, that is probably the safest way to achieve the result that you need. If done correctly you could even eliminate column J from the process entirely.

My way would be to link each checkbox to the corresponding cell in column C, then use the formula below in K2 of sheet 1 and fill down,

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

The bold parts at the start and end are not strictly necessary, but are there to check the selection, it will only show the names if 8 have been selected, any more or any less will leave the list empty.

Jason,
I got this working for 8 with the check boxes linked to Sheet2 C2:C21. Amazing!

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.

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

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.

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

Thank you!

 

[XLawrence]

VBA option
- a duplicate name selected from dropdown in J is rejected
- value also rejected if cell above is empty

Place code in sheet2 code window
(right click on sheet2 tab\ select View Code \ paste code into window that opens)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cel As Range, Rng As Range
    Set Rng = Range("J2:J21")
    If Not Intersect(Target, Rng) Is Nothing Then
        For Each Cel In Target
            If Cel.Value <> "" Then
                If Cel.Offset(-1) = "" Or WorksheetFunction.CountIf(Rng, Cel) > 1 Then Cel.ClearContents
            End If
        Next Cel
    End If
End Sub

Yongle,
I got this to work once and was super excited. Then I cleared contents and I can't get it to work again. When I select from the pulldown in J2 it delete so I can't start over.????

 

[Yongle]

I got this to work once and was super excited. Then I cleared contents and I can't get it to work again. When I select from the pulldown in J2 it delete so I can't start over.????

Did you delete the header in J1 perhaps ?

When back at PC I will post slightly modified code
1 to take account of that
and 2 it occurs to me that user may delete a value in the middle of the range later