none
Drop Down List RRS feed

  • Question

  • Can anyone please tell me how to limit a drop down list so the only people who show on the list are not the ones already assigned to work a shift during that period. My main sheet has a staff member drop down, that looks up names on my variable sheet. Displayed below is roughly what I have, when the next staff member is selected I would like the drop down to only display staff members that are available to work during that period, in the example below the drop down should now not show Mike Swan as available for work during that period.

    Main sheet - this is where my drop down I want to populate is


    What I want to do is for the drop down to only show staff member who are not already set to cover during the dates entered so on row three the drop down should not show Mike Swan as he is already covering during the period entered in A3 - B3 - I hope that makes sense.

    Sheet with staff names


    Tuesday, February 26, 2019 9:02 AM

Answers

All replies

  • Hi AbominableSnowman,

    I'm not sure if I can understand what you wan to do in Drop Down List (Data Validation).
    I'm afraid something is missing in your description.
    (a) a list of member name and his/her workable date is needed. or
    (b) specify cells where Drop Down List is displayed.

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, February 26, 2019 11:49 PM
  • Sorry this is not very clear. I am trying to get my account verified so I can create a more clear question with screenshots. As soon as I do I will let you know :-)
    Wednesday, February 27, 2019 8:49 AM
  • Hi AbominableSnowman,

    I live in Tokyo, Japan (UTC +9.:00), so I cannot think about Drop Down List until tomorrow (after 12 hours or so).  I wait for your screenshots, but we have, be patient with each other.  

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Wednesday, February 27, 2019 9:00 AM
  • Thank you. I am still waiting for my account to be verified. As soon as it is I will post the screenshots.
    Thursday, February 28, 2019 7:35 AM
  • Hi,
    I'm wondering why you don't use cloud storage such as OneDrive, Dropbox, etc?
    It would be faster than your account is verified.
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Thursday, February 28, 2019 7:44 AM
  • Account is verified now, so I have added the images - hopefully it will be clearer now, if not I may scarp this question and try to write a new question instead.

    Hi,
    I'm wondering why you don't use cloud storage such as OneDrive, Dropbox, etc?
    It would be faster than your account is verified.
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Until the account is verified you can't use links either.

    Saturday, March 2, 2019 8:39 AM
  • Assuming you have two sheets, one called "Main" in which you want the drop down list and another called "Staff" with the Staff Names in column A which would be used as a source for validation drop down lists in column C on Main Sheet.

    Place the following code on Main Sheet Module and to do so, follow these steps...

    Right Click on Main Sheet Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim wsStaff As Worksheet
    Dim slr As Long, dlr As Long, i As Long
    Dim x, dict
    If Target.Column = 3 And Target.Row > 1 Then
        If Target.Offset(0, -2) <> "" And Target.Offset(0, -1) <> "" Then
            Set wsStaff = Worksheets("Staff")   'Assuming Staff is the Sheet Name with Staff Names, if not, change it accordingly
            dlr = wsStaff.Cells(Rows.Count, 1).End(xlUp).Row
            If dlr < 2 Then Exit Sub
            x = wsStaff.Range("A2:A" & dlr).Value
            Set dict = CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(x, 1)
                dict.Item(x(i, 1)) = ""
            Next i
            
            slr = Cells(Rows.Count, 3).End(xlUp).Row
            
            For i = 2 To slr
                If Cells(i, 3) <> "" Then
                    dict.Remove Cells(i, 3).Value
                End If
            Next i
            With Target.Validation
                .Delete
                .Add xlValidateList, , , Formula1:=Join(dict.Keys, ",")
            End With
        Else
            On Error Resume Next
            Target.Validation.Delete
        End If
    End If
    End Sub

    The above code is Selection Change Event code. That means, as soon as you select any cell in column C on Main Sheet and if the column A and B are not blank, a drop down validation list will appear in the selected cell with names which are not already selected in column C i.e you will only find the unused names in the drop down list.

    Let me know if this is what you were trying to achieve.


    Subodh Tiwari (Neeraj) sktneer

    Saturday, March 2, 2019 2:52 PM
  • I receive

    Run-time error 32811

    Application defined or object defined error

    When I go to debug it takes me to this line: 

                    dict.Remove Cells(i, 3).Value

    Do you have a sample spreadsheet with this working?

    Saturday, March 2, 2019 9:56 PM
  • Sure. Download and the test the file from This Link.

    Subodh Tiwari (Neeraj) sktneer

    • Proposed as answer by Ashidacchi Monday, March 4, 2019 12:19 PM
    Sunday, March 3, 2019 3:23 AM
  • Hi,

    I've made an alternative and shared it via OneDrive.
    Please download "DropDownList_01.xlsm" and try it.
      

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Sunday, March 3, 2019 4:08 AM shared again
    Sunday, March 3, 2019 3:54 AM
  • Thank you both for your samples. The only problem is that when a row is added with dates in a period where one of the people would be available to work, they are still excluded from the list. I have attached a couple of images to show what I mean.

    So if the staff member is not already assigned to work during the dates entered in column A & B they will still show up on the list so in the first image below on row 3 Dan Smit should still be on the list as he is available to work the dates entered even though he has already been assigned to a shift. In the second image Mike Swan should be available as although he is assigned a shift, but, the shift is not within the period in column A & B. I hope this makes sense. 

    Sunday, March 3, 2019 11:42 AM
  • Hi,

    I'm not sure if I can understand your requirement.
    I suppose you miss more information: the combination [Member] - [available/workable date (from)] -- [available/workable date (to)].

    Sorry, I'm not good at English (I can hardly explain what I want to say).

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Sunday, March 3, 2019 11:51 AM
  • Sorry, my english isn't very good so I am having difficulty explaining - your English is far better than mine :)

    I want any staff member that has not already been assigned to work in the time period entered in cell A & B to still show on the list of people available even if they have already done a shift. So the same staf member can appear a number of times in column C. But, when entering a new record if they are already working a shift during the dates entered in column A & B they will not show on the list. I am having great difficulty explaining this.

    Sunday, March 3, 2019 1:14 PM
  • i AbominableSnowman,

    Thank you for explanation.

    I'd like to confirm:
    Can I assume that only one staff does a shift work on a single day (between Shift Start and Shift End)?

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Monday, March 4, 2019 2:16 AM
  • i AbominableSnowman,

    Thank you for explanation.

    I'd like to confirm:
    Can I assume that only one staff does a shift work on a single day (between Shift Start and Shift End)?

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    No, between the shift start and end there may be more than one staff member working. Some staff memeber may also be working longer dates than others even though part of the shift falls on the same date as others.
    Monday, March 4, 2019 11:17 AM
  • Also sorry for delay between responses even though I have set alerts for this I do not seem to be getting alerts when a comment is added.
    Monday, March 4, 2019 11:18 AM
  • Please download and test This File and let me know if it works as desired.

    Subodh Tiwari (Neeraj) sktneer

    Monday, March 4, 2019 12:00 PM
  • Hi AbominableSnowman,

    Thank you for your reply.  
    I can understand your requirements. But I'm wondering if we can satisfy them, i.e. how can we exclude staff from drop down list before he/she is selected?

    I'm afraid you need to change your idea that you select someone in drop down list. How about using something like Gantt chart?
    Gantt chart sample:
      
    It is almost fully manual work to assign each staff with shift days. 

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Monday, March 4, 2019 12:12 PM
    Monday, March 4, 2019 12:11 PM
  • That is awesome. Thanx so much!!
    Saturday, March 9, 2019 8:20 AM
  • You're welcome!

    Thanks for the feedback.


    Subodh Tiwari (Neeraj) sktneer

    Saturday, March 9, 2019 9:25 AM