+ Reply to Thread
Results 1 to 25 of 25

create a drop down which allows multiple selection and change content of table based on th

  1. #1
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    create a drop down which allows multiple selection and change content of table based on th

    need your help guys...

    i am creating a report which should i hope should be as dynamic as i can. in this report i need

    a. create a drop down which allows multiple selections (i.e. in week, i can choose 1, 2 3, or all depending on what i click)
    b. table will change depening on the combination of the dropdown selected
    c. column L in the table will be automatically sorted from highest to lowest

    super thanks in advance for your time.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Where is the data in the table coming from?
    Please click *Add Reputation if I've helped

  3. #3
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Should the drop down populate the data in the table using data extracted from elsewhere or are you just trying to perform some kind of filter on the data already in the table?
    Last edited by kadeo; 08-10-2015 at 09:25 AM.

  4. #4
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    the data will be coming from a master database file... basically the same table where we will encode a consolidated data. or any other suggestions to make it easier?

  5. #5
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    So you want to provide a drop down that will contain all of the week numbers that exist in the source data as well as an "All" option, the user makes their selection and the table below then displays all data from the source that have a matching week number (or all the data) sorted by Total Score? That about right?

  6. #6
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    yes... but note that there are also other drop downs... like the ROLE, TEAM, and USER... the table should appear whatever is chosen in all the dropdowns... so 1 could have a view of week 1 and 2 only for user MID005 or he can choose to have all weeks for CRVR team and for all process expert.. hope i didn't make it too complicated??

  7. #7
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    No I'm sure it's mostly possible. You see with the week numbers though, you said that a user might choose to view weeks 1 & 2 only. So do you expect a user to be able to choose any number of weeks to view? i.e. Might they choose to view week 1, 5 & 8 say? The same with the other options? Drop downs tend to only be useful for single item selections. It might be better to use listboxes?

  8. #8
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    hmmm i am not familiar with list boxes but if it would be able to do what is required then ill be fine with it... can you help me on this?

  9. #9
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Yes man, no problem. I'll get a proper look at that for you tomorrow.

  10. #10
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    great.. i'll look forward to your reply dear

  11. #11
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Hey melody

    Still working on this one. It looks like a userform containing listboxes is the way to go.

    Quote Originally Posted by melody10 View Post
    yes... but note that there are also other drop downs... like the ROLE, TEAM, and USER...
    I don't see a column of data for "USER". Is that the APM ID or is it somewhere else?

  12. #12
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Quote Originally Posted by melody10 View Post
    a. create a drop down which allows multiple selections (i.e. in week, i can choose 1, 2 3, or all depending on what i click)
    b. table will change depening on the combination of the dropdown selected
    c. column L in the table will be automatically sorted from highest to lowest
    Melody, you see before I get much further into this, you could achieve points a. & b. with a simple filter applied to the headers of the table. Additionally, if the table was sorted by column L anyway, then c. would be covered too! Will that not be sufficient?

  13. #13
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    hi dear... thanks for getting back to me...the idea is that source data will be coming from a master database... and only the data chosen in the list box will appear on that table.

    i was thinking if its possible that when i open the excel file... a list box (or user form) will appear then i choose the parameters in the list box... and when i click on a button, only the desired data will appear on the table.


    reason being is that if all data will be available and filters will only be used, it would create a very very long list... plus not all data should always be visible..

    really really need your help on this

  14. #14
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    hello... yes APM ID and user is the same

  15. #15
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Quote Originally Posted by melody10 View Post
    hi dear... thanks for getting back to me...the idea is that source data will be coming from a master database... and only the data chosen in the list box will appear on that table.

    i was thinking if its possible that when i open the excel file... a list box (or user form) will appear then i choose the parameters in the list box... and when i click on a button, only the desired data will appear on the table.


    reason being is that if all data will be available and filters will only be used, it would create a very very long list... plus not all data should always be visible..

    really really need your help on this
    Yup, that's ok Melody. Just wanted to be sure I wasn't trying to replicate existing excel functions. I'm enjoying this one!

  16. #16
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    Any news?

  17. #17
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Hey. I've got the user form and input sorted. Just working on the reporting part now.

  18. #18
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Hi Melody

    Ok so here's your file back.
    (File deleted, see below post)

    Because it now includes macros, it is a ".xlsm" file instead of the ".xlsx" file you uploaded. If you don't have macros turned on, excel will warn you.

    I've tried to follow your instructions as closely as possible with the emphasis on "dynamic" for both you and your users.

    Stuff you need to know:
    1. You were using tables so I've kept that as is. I had literally zero experience coding for tables prior to this so I had a bit of a learning curve today! Like I said, I was enjoying this one.
    2. As I've coded this to work with tables, the source data MUST be a table.
    3. In the VBA project explorer you'll see two modules and the Userform. So that you can move stuff around on the worksheets, I set up two procedures (one at the top of each module) that will identify where everything is for the rest of the code. If you change the layout of the worksheets, you MUST update these procedures. I've noted what everything does in them so it should be easy enough to follow. But essentially, you'll need to update these two procedures if you want to do any of the following:
      • Change where you want the criteria to be listed
      • Change where you want the report table to display
      • Change the name of the sheets
      • Relocate the source data table to a different sheet
      • Rename the source data table
      • Change the position of the columns in the source data
      • Change the default "sort by" option
      This should allow for a bit of future proofing without the need to update lots of code.
    4. The code makes use of Excel's filtering and sorting options which MAY give unusual results if there are any fields that are left blank in the source data.
    5. The data that populates the listboxes is taken dynamically from the source data itself. This makes sure that the only data in these boxes is relevant and that no data that gets added to the source later is omitted. If you identify any issues with the data in these listboxes then you'll have to address the source data to fix it. The lists are displayed alphabetically and if there are more items in the list than can be displayed in the listbox, scroll bars will appear.
    6. You said you wanted it to be sorted by column L from highest to lowest but it seemed to make more sense to give the user this option so I have included that in the form as well. It defaults to "Total Score" and "Descending" which is the same as column L and highest to lowest
    7. The data in the "Sort By" drop down box is also taken dynamically from the source data. It is just a list of the source data headers. This is listed in the order they appear in the source data from left to right. This way, if columns get added, removed or repositioned the data in the box will still be accurate. Again, any issues with this data and you'll have to addresss it in the source data.
    8. A user can click any number of items in each listbox.
    9. There are "Select all" checkboxes for each listbox.
    10. If no items are selected in a listbox then that is also treated as a 'select all' otherwise no data would be returned.
    11. When the user hits "Done" the data is filtered with excel's built in filter function using the criteria selected by the user. The old Report is deleted. The filtered data is copied over to the report sheet to become the new report and then it is sorted.
    12. If there are no results based on the criteria selected, then a message is displayed advising. (Select week 8 and APM ID "aaaa" to see this in action)
    13. The generated report is another table called "ReportData" with which the user can do whatever tabley things they need to do.
    14. The report only contains the results of the selected criteria. That is, it does not contain the rest of the data but hidden or something like that.
    15. Any filters applied to the source data are removed and the data itself is not modified in any way.

    I think that just about covers everything. Will you give it a test drive and let me know how it works out for you?

    Last edited by kadeo; 08-14-2015 at 04:07 AM.

  19. #19
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    I've just noticed it crashes when sorting by week number. I think it's probably because of the # symbol. I'll have a look at fixing that tomorrow.

  20. #20
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    Wow!! Macro overload for me.. i'll check the attachment and let you know if anything. Either way super super thanks

  21. #21
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    Quote Originally Posted by kadeo View Post
    I've just noticed it crashes when sorting by week number. I think it's probably because of the # symbol. I'll have a look at fixing that tomorrow.
    Here you are Melody. I used a different method for sorting which seems to have fixed it.
    books12.xlsm

    I'll delete the other copy.

  22. #22
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    i was playing around with your file and i am at a loss for words. it was simply wow!!!

    one question though, i know everything was based on "table" as per my sample... just wondering if it would be too much revisions if i don't use a table instead? just thinking that some of our users might find it easier to encode excel in a normal way.

    but again if its too much... then i am ok with the file. it exceeded my expectations. thank you very very much

  23. #23
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    It turns out tables give you a bit more flexibility when it comes to designing the front end, the spreadsheet itself. Because you can refer to specific columns in a more organised way, it makes moving the table around not so big of an issue. Essentially the week # column of that table will always be the week # column of that table whether it's at start, middle or end of it or whether the table starts in cell b2 or c9 or wherever.

    It can be changed to use ranges and standard cells, in fact I had started that way before I noticed you'd used tables, but you might lose some flexibility, or at the very least have more code to update whenever you make those type of changes.

    What do you think?

  24. #24
    Forum Contributor
    Join Date
    06-24-2013
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    198

    Re: create a drop down which allows multiple selection and change content of table based o

    ahhh ok ok... thanks for that input... i'll stick with tables then. again... my deepest thanks dear

  25. #25
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: create a drop down which allows multiple selection and change content of table based o

    My pleasure Melody. If you're happy will you mark the thread solved, maybe consider adding rep?

    And if you've any issues with it feel free to PM me.


+ 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. Replies: 1
    Last Post: 06-25-2015, 07:29 AM
  2. Change cell content depending on selection from drop down list
    By mack4n in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2015, 04:17 AM
  3. Replies: 1
    Last Post: 04-03-2015, 06:00 PM
  4. [SOLVED] Need active X list box content to change based off of combo box selection
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2014, 04:26 PM
  5. Replies: 2
    Last Post: 07-15-2014, 08:24 PM
  6. Create a sheet with content dependent on the selection of a drop-down list
    By bbaumgardner22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 04:25 PM
  7. Row color change based on drop down selection
    By bigbluesfan22 in forum Excel General
    Replies: 7
    Last Post: 08-01-2012, 09:15 AM

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