Automatically refresh filtered lists

[alreadytaken]

Hi,

I have found a few bits of code through googling that profess to automatically update a filtered list when the master data changes, however they do not seem to work in quite the way I want them to.

For example, I have a master list of jobs and I want to assign members of staff to each job. I then want separate sheets that show the job list for each staff member. So, on the "Bob" sheet, the list is filtered to only show jobs assigned to Bob. If I make a change on the master sheet (for example give Bob an extra job) is it possible to have the Bob sheet automatically update the filtered list to show the new job? Currently, I have to click the reapply button on Bob's sheet to make the extra job show up.

Excel 2016, demo file attached.

Many thanks in advance for your help.

[dflak]

You can use a pivot table for this. First of all, I converted the data range on the master sheet to an Excel table. The reason for this, is that Excel tables "know" how big they are, so any formulas or pivot tables built off of them will reference the correct amount of data always. So when you add a job to the table, the table expands to include it and so does the pivot tables.

You might also want to consider adding data validation to your fields. Such as columns B and C are times and a valid list of staff members. This will assure consistent spelling and more accurate data.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel

Here is more information on data validation: http://www.utteraccess.com/wiki/Data_Validation