How to add sorting/lookup function that filters master data file based on particular columns
I have a large master data file that essentially lists out training classes and assigns them to particular Lines of Business on particular dates. It is a calendar/scheduling function. I then bucket the information into Quarters (Q1,Q2,Q3,or Q4). I would like to create a separate tab that only shows the classes for a specific block of time(ex. Q2). A training may launch for one Line of Business in Q2 and another in Q3.
In the attached file, the 'Master Data' tab shows (by course) a launch date, type of launch, and then the bucketed 'reference' for each Line Of Business (each LOB has a block of these 3 dimensions). How would i be able to filter on the last blocked dimension, reference, so that I only show the data with Q2 for example?
In the attached file, i am specifically referencing 'master data' tab, columns O-Q for rows 9-12. This is the LOB "Corp Overhead" for these courses.
I would like to filter from the drop-down in the 'Calendar-time' tab by the "Reference" column.
| Attachment | Size |
|---|---|
| How-to-COMPLIANCE_CALENDAR.xls | 269 KB |
How to add sorting/lookup function that filters master data file
Table structure is a little bit tricky, but...
For the beginning, I suggest Advanced Filter to use drop-down value as criterion in filter.
Advanced filter works in a was that you specify criterion (Q1, Q2 etc.) and it lists only rows from the source table that are from e.g. "Q2"). Maybe the second table needs some tuning-up, but could be done.