Help to use Slicer to display (x-ref) data from multiple different columns

"I get by with the help of my friends." or in this case helper columns and helper pivot tables.

On the pivot sheet in column O I have a pivot table that shows only company names. This pivot table is connected to the same slicer that controls the main table.

I overlaid this pivot table with a named dynamic range called List_Company: =OFFSET(Pivot!$O$4,0,0,COUNTA(Pivot!$O:$O)-1,1). Here is some information on named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

Back on the Data sheet, I converted the range of data to an Excel table. Excel tables "know" how big they are so when you add or delete rows, formulas, charts and pivot tables built from them do not have to be changed. They will always reference the exact amount of data they need. Also Excel tables "remember" and copy down formulas automatically. See this wiki for information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

So when you are ready to use real data, you can remove the data rows (see the wiki on how to do this) and copy in the new data.

The new helper column Also Rep has the formula: =ISNUMBER(MATCH([@[Account Name]],List_Company,0)) which reads List_Company, which in turn are only those companies associated with the selected name. So this is true if the account name is on the list of companies shown in column O.

Which brings us to the pivot table in columns L & M. This shows the list of people who are also associated with the companies for the selected person.

So far so good without VBA. But there is an issue. The pivot table in columns L & M does not refresh automatically. So I added a short bit of code to refresh it when the other tables are refreshed.



Back on the data sheet, I added this to the