+ Reply to Thread
Results 1 to 34 of 34

Advanced Dropdown list with filtering

  1. #1
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Advanced Dropdown list with filtering

    Hi there.

    I'm creating an employe plan

    Let's take Week 1 as an example.

    I have 50 employee available.

    Monday I need to select 5 person to start at 07:00 am. in department 1
    Monday I need to select 6 person to start at 08:00 am. in department 2
    Monday I need to select 8 person to start at 08:00 am. in department 3
    And so on for the rest of the week.

    To ensure, that i'm not picking the same person for 2 department, the same day, I'm using Dynamic List with Formulas; Name Manager
    and Datavalidation; List, to check if the name has been used before. Using -> "Name List1" - "Not Used Row" - "Not Used List"
    Week1.JPG

    The problem with this is, that the Formulars and Datavalidation is growing up to enormous size.
    Because I have to create a new Formular and Datavalidation for every single day, and it's a lot for a whole year.

    I want help to..

    If I have selected Monday-Department 1 "Name 1" from the Dropdown list, the "Name 1" should not be in the Dropdown list any more, for Monday Week 1.
    All the names must be accessible again on Tuesday, and so on for the rest of the year.

    I have read about VLOOKUP and Object, but coulden't find the solution.

    Hope you can help :-)

    Kind regards
    Jan Iversen
    Attached Images Attached Images

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Advanced Dropdown list with filtering

    Hi Jan,

    I think you want to use Conditional Formatting and highlight any name that is duplicated. That way if you add a person to the schedule and they are already on it, you will know...

    http://www.excel-easy.com/examples/find-duplicates.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    @MarvinP - Agreed - my thoughts exactly

    @JAKIV see attached workbook
    change a value to duplicate a name in a column

    all done with ONE conditional format rule
    Attached Files Attached Files
    Last edited by kev_; 04-13-2018 at 06:56 PM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  4. #4
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi there.

    Thank you for your suggestion to solve my problem.

    You are probably right that this is the easiest way to see if the same is chosen twice.
    The opportunity to choose the same 2 times is of course still present.
    It does not require Formulars or Data Validation, so it's a reasonably easy solution :-)
    I'll just wonder if that's the solution for me.
    My wish was that the person would disappear from the Dropdown list ...
    I would probably use the solution if not others come with other solution suggestions.
    Best regards
    Jan Iversen

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    See the attached which has possible approach but it would help if we had a small version of your file.

    On Sheet2 (using Kev's file!)

    Column A is your Employeee list

    Column B has a flag ("x") set if employee chosen

    Column C is a dynamic list (DV_Names) based on those NOT selected i.e column B is blank

    On Sheet1

    Column O has a DV based on "DV-Names" so as each person is selected the list is adjusted

    Using a single Dynamic range obviously requires B to re-set to blank for next selection: might require VBA.

    As you have a Large number of DV lists this may not be viable unless the number of lists can be reduced.

    EDIT: one issue with this approach is you cannot (easily) deselect an employees who has been wrongly assigned: e.g. Employee 6 was selected but it should have been Employee 7.

    With the CF approach this problem will not occur.

    Just a thought ...

    Unless you need to keep "historical" record of employees /department by week , why not generate 2 (or 4 max) weeks worth and then "circulate" so you have 2 (or 4) lists:

    Odd weeks/Even Weeks (Week 1 & Week 2 / Week 3 and Week 4 /......) so Week 1 is "overwritten" by Week 3 and "week 2" by "Week 4"
    Attached Files Attached Files
    Last edited by JohnTopley; 04-14-2018 at 06:16 AM.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    Or you could use a bit of VBA

    If a "duplicate" choice is made then the cell contents cleared and the display bar tells you where to find the matching value
    - uses a function with same condition as conditional formatting

    Reject name.jpg

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi
    How nice is that so many will help me with this challenge.
    These are all some really good suggestions.

    JohnTopley, Yes,,I have to book a year at a time ,, unfortunately.

    JohnTopley Quote: Using a single Dynamic range obviously requires B to re-set to blank for next selection: might require VBA.
    This is where the problem lies :-)

    kev_ Good suggestion. It minimized a lot of work with Formulas; Name Manager and DV. That's nice.

    But when I plan with 50 employees or twice, it would be great if I in the Dropdown list could only see who I have not chosen yet, instead of still having everyone in the Dropdown list.
    I still hope that we can succeed.

    Am I too demanding :-)

    Best regards
    JAn Iversen

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    You will only be able to get a reducing list using the method I propose. VBA could be used to achieve the same without the need for the column B tag.

    However you are still left with the problem of an invalid selection being made: how to correct it when the DV list has been regenerated.?

    I find booking a year ahead interesting particularly if one had high churn of staff!!!

    It would be helpful if you could post a small sample file.
    Last edited by JohnTopley; 04-14-2018 at 04:26 PM.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    Try this - it is exactly what you requested

    Dropdown is dynamic in any cell in rows 2:30
    - all possible names (Names Coumn A) are passsed into an array
    - each name is tested against those already used in "active" column using COUNTIF
    - unused names are added to a string
    - resultant string used as validation formula

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi

    Here's a small part of the template I'm using. Not the Emploee Planner :-)

    Only columns A and E ,,, are used to select a person.
    As you can see, I do not have the opportunity to choose the same person twice.

    However, this setup requires 5 columns for each day (A, B, C, D + "All List A") to work.

    When I use Danish Excel, I have tried to translate the words used in the formulas .. :-)

    Column A (The first 2 row)
    Monday Week 1 With (NameManagement)
    =Displacement(Sheet1!$D$2;0;0;COUNT(Sheet1!$C$2:$C$50);1)
    =Displacement(Sheet1!$H$2;0;0;COUNT(Sheet1!$G$2:$G$50);1)

    Colume B
    Name List1 (The first Row)
    Datavalidation from ='All List'!A2

    Colume C (The first 2 row)
    Not Used Row
    =IF.ERROR(IF(COMPARE(B2;$A:$A;0)>=1;"";"");ROW())
    =IF.ERROR(IF(COMPARE(F2;$E:$E;0)>=1;"";"");ROW())

    Colume D (The first 2 row)
    Not Used List
    =IF(ROW(B2)-ROW(B$2)+1>COUNT(C$2:C$50);"";INDEKS($B:$B;SMALLEST(C$2:C$50;1+ROW(B2)-ROW(B$2))))
    =IF(ROW(F2)-ROW(F$2)+1>COUT(G$2:G$50);"";INDEKS($F:$F;SMALLEST(G$2:G$50;1+ROW(F2)-ROW(F$2))))

    Hope you understand the meaning.


    Best regards
    Jan Iversen
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    Some thoughts:

    How are you handing availability for the Year ?

    1. Planned absence such as Vacation/Training/ ....

    2. Unplanned absence such as Sickness (short/long term)

    For the former (planned) you need a DAILY availability list of all employees (365/260 (working days) named ranges?)

    In this case, you could use the approach I offered, having a "selected" column for each day (simpler than your posted file): and/or use/combine with Kev's VBA option(s).

    For the latter (unplanned), you need to consider how you change any daily rota for unplanned absence. How is someone removed and then replaced in the rota?

    Are employees rotated round departments: if so, does this have any impact? What about "skill" levels: all employees skilled for all departments?

  12. #12
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi there..

    JohnTopley //Some thoughts:

    JohnTopley // How do you deliver availability for the year?

    1. Scheduled absence as vacation / education / ....
    :: We know the schedule for when our students are going to school. It is typically 6 weeks spread over 3 times in a year.

    2. Unplanned absence as illness (short / long term)
    :: Of course, can not be planned.
    If a department is understaffed, we can often move a person from another department.

    3. For the forms (scheduled) do you have a daily availability list of all employees (365/260 (working days) named intervals?)
    :: As I chart everything vacation, relaxation, etc. into the work plan, they must disappear from the dropdown list and are therefore unavailable.

    4. Have employees rotated around departments: if yes, does this have any influence? What about "skill levels": All employees skilled in all departments?
    :: There are, of course, someone I can not move to other areas.
    I have thought of it, but it must come in a later version :-)

    :: I have added a scaled-down version of my workplanner.
    : This is about 30 employees.
    Even here, it can be difficult to see who I forgot to assign a work area.
    In fact, I have overlooked a person in Week 1, ,,, Is it easy to find the person?
    And then we actually have to have many more people in the Dropdown list.
    If now all I had choices disappeared from the Dropdown list, it was easy to see.

    Best regards
    Jan Iversen
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    I'll wait to see if Kev replies (re using VBA) and what his views are. The layout is not the most friendly: a better format would be to have Week Number in column A and thus a single (admittedly long) table.

    VBA is probably the way to go but the decision is yours as you have use and maintain whatever solution you decide on.

  14. #14
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    I'll wait and see if Kev replies (re using VBA)
    and what his views are



    I will re-engage tomorrow
    Last edited by kev_; 04-15-2018 at 04:18 PM.

  15. #15
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Looking forward to it in excitement :-)

  16. #16
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Ohhh. I've completely overlooked something ... Sorry ...
    kev_ has made the solution d. 04-14-2018, 11:49 PM.
    I just did not see it.
    It does exactly what I want ...
    I'm very, very sorry.
    1000 thanks for all your contributions and of course a special thanks to kev_ for the solution ..
    Many thanks to JohnTopley, who tried hard to solve it, but did not understand why I could not use Kev_ fine solution. :-)
    Greetings
    Jan embarassed.

  17. #17
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Ohhh. I've completely overlooked something ... Sorry ...
    kev_ has made the solution d. 04-14-2018, 11:49 PM.
    I just did not see it.
    It does exactly what I want ...
    I'm very, very sorry.
    1000 thanks for all your contributions and of course a special thanks to kev_ for the solution ..
    Many thanks to JohnTopley, who tried hard to solve it, but did not understand why I could not use Kev_ fine solution. :-)
    Greetings
    Jan embarassed.

  18. #18
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Eeeh.
    I don't know how to close this Thread as Solved :-)

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    Many thanks to JohnTopley, who tried hard to solve it, but did not understand why I could not use Kev_ fine solution. :-)
    .... meaning ? I understood perfectly Kev's solution and how it could be applied.

    How was I to know you had not seen Kev's solution ???
    Last edited by JohnTopley; 04-16-2018 at 03:54 PM.

  20. #20
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Yes, of course you did. I can see that.
    It was just formulated a little wrong.
    It was me who did not understand you and kev_ :-)
    You tried to convince me that Kev_'s solution was right for me.
    I just did not understand it ..
    Now i do..
    quote:How was I to know you had not seen Kev's solution ???
    Of course you could not know that ..
    Best regards
    Jan Iversen
    Last edited by JAKIV; 04-17-2018 at 12:55 PM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  22. #22
    Forum Contributor
    Join Date
    06-25-2016
    Location
    india
    MS-Off Ver
    MS Office 365(Win 10) Home Ed 2016
    Posts
    134

    Re: Advanced Dropdown list with filtering

    attached a small data and with drop down + vlookup, but result shows only one row. I need when list column is selected all related lines should be display
    Attached Files Attached Files

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  24. #24
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi kev_

    Thanks for the help and time it took you to make the Dropdown list for me.

    It' great.
    However, I have a little desire for change.
    I'm using the A-S columns
    But there should be no Dropdown list in Column A + B + J, as it is static.
    There must be Dropdown list in Column C + D + E + F + G + H + I

    And again, there must be no Dropdown list in Column K + L + T, as it is also static.
    There must be Dropdown list in Column M + N + O + P + Q + R + S

    In addition, there should be no dropdown list in row 1 + 2 + 3 + 4 and 44 + 45 + 46 + 47 and 84 + 85 + 86 + 87 and so on.
    There is therefore a jump of 40 rows for the next time there are 4 rows that do not have to have a dropdown list.

    Is there anything that can be built into the VBA, or do I wish too much now?

    Best regards
    Jan Iversen

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advanced Dropdown list with filtering

    In addition, there should be no dropdown list in row 1 + 2 + 3 + 4 and 44 + 45 + 46 + 47 and 84 + 85 + 86 + 87 and so on.
    There is therefore a jump of 40 rows for the next time there are 4 rows that do not have to have a dropdown list.
    This does not align with your "Workplan.xlsx" file so please post a definitive file to ensure any solution meets its requirement.

    From me (in post #13)

    The layout is not the most friendly: a better format would be to have Week Number in column A and thus a single (admittedly long) table.
    This would avoid all the complications with having to skip rows and columns.
    Last edited by JohnTopley; 04-18-2018 at 09:54 AM.

  26. #26
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi
    I think user friendliness and clarity disappear if I have Week Number in column A. But maybe it's just me. :-)
    I have now edited Workplan.xlsx to fit rows and columns, as I described earlier.
    But, it's not so important if it just fits exactly with this, as I'm sure to add new rows next month. Then it will not fit again.
    I may even add som extra column, who is not there yet.
    The most important thing is just that I have the VBA code, which I can subsequently correct.
    Best regards
    Jan Iversen
    Attached Files Attached Files

  27. #27
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    But there should be no Dropdown list in Column A + B + J, as it is static.
    There must be Dropdown list in Column C + D + E + F + G + H + I
    And again, there must be no Dropdown list in Column K + L + T, as it is also static.
    There must be Dropdown list in Column M + N + O + P + Q + R + S
    In addition, there should be no dropdown list in row 1 + 2 + 3 + 4 and 44 + 45 + 46 + 47 and 84 + 85 + 86 + 87 and so on.
    There is therefore a jump of 40 rows for the next time there are 4 rows that do not have to have a dropdown list.
    1. Theabove is easy with VBA
    - If Target.Column is any of these 1, 2, 10, 11, 12, 19 Then NO DROPDOWN
    And rows we can do like this
    - use column T and put "X" in each row 1,2,3,4,44,45,46,47...etc
    - If column T = "X" Then NO DROPDOWN

    2. What is more difficult is how to tell VBA which rows to include together when checking if a name has already been used
    - VBA needs to check only the cells that are together in the "current" block
    - I am trying to find a simple code to do that
    - VBA can use the range of cells in column T with values <> "X"

    I will update the thread with the solution tomorrow after I have tested a few ideas

  28. #28
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Sounds like a good solution :-)
    Looking forward to seeing it.

    Best regards
    Jan Iversen

  29. #29
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    I just noticed you said column T is static
    - so you are already using column T ???
    I have therefore used column U for the "x"

    INSTRUCTIONS
    - put "x" in column U if dropdown is not required
    - all other cells in column U must be BLANK!!
    - place all code in sheet module
    Please Login or Register  to view this content.
    minor change
    Please Login or Register  to view this content.
    as before
    Please Login or Register  to view this content.
    You do not need to use procedure below if you do not want - allows you to double-click in column U and "x" changes to blank or "blank" changes to "x"
    Please Login or Register  to view this content.
    Last edited by kev_; 04-19-2018 at 11:08 AM.

  30. #30
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Thank's kev_

    I do not know if it's because I do not understand it.
    I have added "DynamicDropdown Overview.xlsm" so you can see what I mean :-)
    I have also added "DynamicDropdown -With new VBA Code.xlsm" where I have inserted your new VBA code.

    Can not fully understand that by entering X in column U
    Quote: - put "x" in column U if dropdown is not required
    - All other cells in column You must be BLANK !!

    Do you mean only in cell U1 or in all the cells in U where there should be no dropdown list?
    I want to write something else in the static fields.
    I would like to write some formulas in the static fields.

    Best regards
    Jan Iversen

  31. #31
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    Do you mean only in cell U1 or in all the cells in U where there should be no dropdown list?

    In addition, there should be no dropdown list in row 1 + 2 + 3 + 4 and 44 + 45 + 46 + 47 and 84 + 85 + 86 + 87 and so on.
    so... U1,U2,U3,U4.....U44,U45,U45,U46....U84,U85,U86,U87...


    You should be able to use copy/paste and do this very quickly

  32. #32
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi kev_
    I've been out of office for a while now, but now I'm back and would love to have the Dropdown list to work.
    Now I've tested your solution over and over again.
    But I can't make it work as you say.
    Can we change the VBA code a bit, so if there is an "x" in the column's top cell Row A, then there should be no dropdown in this column.
    The same must be true in rows.
    Here we use column "V" If there is an "x" in a Cell in column "V" then there should be no Dropdown in this row.
    So I think it will be easier to control.
    Hope you still want to help me.
    Best regards
    Jan Iversen

  33. #33
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Advanced Dropdown list with filtering

    I am very busy at the momemt - away from home until July
    I will try to look at this for you on Sunday

  34. #34
    Registered User
    Join Date
    02-13-2018
    Location
    Denmark
    MS-Off Ver
    2016
    Posts
    24

    Re: Advanced Dropdown list with filtering

    Hi there..

    I finally made it work.
    I've changed some that Kev_ made for me.
    However, it was a great help to get started.
    I have inserted the new VBA script I'm currently using.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim zone As Range, c As Long, r As Long
    Dim iFirstRow As Integer
    Dim iLastRow As Integer
    Dim sBox(10) As String
    Dim bWithinBox As Boolean

    'Hardcoded Namedranges of boxes
    sBox(1) = "Boks1"
    sBox(2) = "Boks2"
    sBox(3) = "Boks3"
    sBox(4) = "Boks4"
    sBox(5) = "Boks5"
    sBox(6) = "Boks6"
    sBox(7) = "Boks7"
    sBox(8) = "Boks8"
    sBox(9) = "Boks9"
    sBox(10) = "Boks10"

    If Target.CountLarge > 1 Then Exit Sub

    c = Target.Column: r = Target.Row

    If c > 20 Then Exit Sub 'T is last column
    'If Not Intersect(Target, Range("A:B, J:L, T:T")) Is Nothing Then Exit Sub 'static columns

    'Check if cell is within one of the boxes, else Exit Sub
    For i = 1 To UBound(sBox)
    If Not Intersect(Target, Range(sBox(i))) Is Nothing Then
    bWithinBox = True
    Exit For
    End If
    Next i
    If bWithinBox = False Then
    Target.Validation.Delete
    Exit Sub
    End If

    'specify range to include dynamic dropdown
    Set zone = Cells(r, 21)
    If zone.Value = "x" Then
    Target.Validation.Delete
    Exit Sub 'excluded rows
    ElseIf zone.Offset(-1) <> "x" Then
    Set zone = zone.End(xlUp).Offset(1) 'first blank cell in column U
    End If

    ' 'find First row of box
    ' i = 0
    ' While Cells(Target.Row + i, Target.Column).Interior.Color <> vbBlack
    ' iFirstRow = Target.Row + i
    ' i = i - 1
    ' Wend
    '
    ' 'find last row of box
    ' i = 0
    ' While Cells(Target.Row + i, Target.Column).Interior.Color <> vbBlack
    ' iLastRow = Target.Row + i
    ' i = i + 1
    ' Wend

    Set zone = Range(Cells(Range(sBox(i)).Row, Target.Column), Cells(Range(sBox(i)).Row + Range(sBox(i)).Rows.Count - 1, Target.Column)) 'blank range in column U

    'add dropdown
    Call DynamicValidation(Target, UnusedNames(zone))

    End Sub

    Private Function UnusedNames(zone As Range) As String
    Dim mystr As String, a, arr

    arr = Sheets("Names").Range("A1").CurrentRegion.Value
    For Each a In arr
    If WorksheetFunction.CountIf(zone, a) = 0 Then
    If mystr = "" Then mystr = a Else mystr = mystr & "," & a
    End If
    Next
    If mystr = "" Then mystr = "empty list"
    UnusedNames = mystr
    End Function

    Sub DynamicValidation(Target As Range, myString As String)
    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=myString
    End With
    End Sub

    Thanks :-)
    Best regards
    Jan Iversen

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advanced Filtering Using a List
    By clueless12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2015, 09:33 AM
  2. Advanced filtering with a criteria list
    By jimmy13 in forum Excel General
    Replies: 4
    Last Post: 12-19-2013, 01:58 PM
  3. Dropdown list options/Auto-filtering?
    By akdr in forum Excel General
    Replies: 2
    Last Post: 08-29-2012, 10:53 AM
  4. Advanced Dropdown List
    By Zanno in forum Excel General
    Replies: 3
    Last Post: 08-10-2011, 04:11 AM
  5. Filtering Using Dropdown List
    By lemuel in forum Excel General
    Replies: 0
    Last Post: 06-16-2011, 11:08 AM
  6. How to make a dropdown list that is more advanced
    By surfol in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-10-2011, 07:21 AM
  7. Replies: 3
    Last Post: 05-15-2011, 11:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1