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

Where is the data in the table coming from?

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?

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?

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?

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

Hey melody

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

Originally Posted by melody10

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?

Originally Posted by melody10

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?

Originally Posted by melody10

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!

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

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?

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.

Originally Posted by kadeo

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.

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?

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.