none
Dynamic Lists RRS feed

  • Question

  • I have a set of data in worksheet "NLIM_Data" with numerous columns, one of these is "Date", any new data is added to this worksheet by means of a Userform.

    I have another worksheet "Dashboard" which summaries certain statistics generated from the "NLIM_Data" worksheet, dictated by year.

    I have the first year in Cell B27 and a second year in cell B28, the second is 1 year less than the first. The first year is selected by a dropdown list "Year" updated each year.

    Rather than adding a year incrementally to this list i would like the year to be populated by the date column, is there a way this can work??

    Steve

    Friday, December 29, 2017 10:23 PM

All replies

  • Hi Steve,

    I'm making a sample for you, and would like to confirm:
    (1) where is a Drop Down List?  on UserForm, on sheet "Dashboard", or other?
    (2) specify the range of year. i.e. the first year and the last?  e.g. 2017-2008 (from this year to the past 10 years) 

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Saturday, December 30, 2017 4:13 AM
    Saturday, December 30, 2017 2:25 AM
  • Good Morning Ashidacchi

    my current list is located within a worksheet called "Lookup", but this is a manual updated list. What I would like is if a new date is added to "NLIM_Data" and this date has a new year it is then picked up by the "Year" list.

    I am trying to set this workbook up so that the next person in this posting will not have to add years manually as they may not be as used to excel as I am.

    The current years in the list are 2016 and 2017. 2018 is about to be added as we roll into a new year, but i will not add this until a new piece of 2018 data is added.

    Regards

    Steve

    Saturday, December 30, 2017 7:06 AM
  • Good Evening (in Japan) Steve,

    I'm afraid your description is vague for me and you don't answer my questions.
    I've shared "DynamicList.zip" ("DynamicList.xlsm" is compressed in it) via OneDrive. 
    Please download & unzip it, and specify your requirements.

    I suppose it would be hard to explain by words. If so, please insert some screenshots in your post, or share them via cloud storage such as OneDrive, Dropbox, etc.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Sunday, December 31, 2017 12:22 AM
    Saturday, December 30, 2017 7:37 AM
  • Hi Steve MW,

    you had mentioned that,"I have the first year in Cell B27 and a second year in cell B28, the second is 1 year less than the first. The first year is selected by a dropdown list "Year" updated each year."

    so it looks like your data is stored like below.

    you had mentioned that,"Rather than adding a year incrementally to this list i would like the year to be populated by the date column, is there a way this can work??"

    code:

    Private Sub Worksheet_Activate()
       Me.ComboBox1.Clear
      Dim x As Integer
          Application.ScreenUpdating = False
         
          NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
         
          Range("A1").Select
         
          For x = 1 To NumRows
             
             Me.ComboBox1.AddItem (Cells(x, 1).Value)
          Next
          Application.ScreenUpdating = True
    End Sub
    
    

    so the data looks like below in dropdown list.

    if this is not your desired result then let us know about that and provide a some snapshot of your data stored in a sheet and show us your desired output.

    we will try to correct the code and try to provide you to fulfil your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Tony---- Wednesday, January 3, 2018 5:32 AM
    Monday, January 1, 2018 4:32 AM
    Moderator